Is it possible to use a variable or parameter in a MySQL view?

Question

I'm trying to create a View as following:

CREATE VIEW v_MyView
AS

SET @par_count := 0;  -- XXX Working in SELECT, but not in a View !? XXX

SELECT
    q1.day,
    q1.count_per_day,
    (@par_count := @par_count  + q1.count_per_day) AS count_sum  -- sums up count_per_day 
FROM
    (SELECT
        DATE(registration_date_time_obj) AS day,
        COUNT(Date(registration_date_time_obj)) AS count_per_day
     FROM  tbl_registration_data
     GROUP BY day
     ORDER BY day
    ) AS q1
;

The select statement itself works fine, just creating a view fails in MySQL since it doesn't accept user variables/parameters within it's declaration i guess.

Is there a way to still create this view with a workaround for the parameter?

Anyways, i'm able to create a similar procedure for the select statement, but that doesn't really solve the problem since i can't call the procedure in another select statement...

Thanks for your suggestions and solutions! (:

Answer 1

MySQL documentation is pretty clear that variables are not allowed:

• The SELECT statement cannot refer to system variables or user-defined variables.

• Within a stored program, the SELECT statement cannot refer to program parameters or local variables.

You can do what you want using a correlated subquery:

 SELECT DATE(registration_date_time_obj) AS day,
        COUNT(Date(registration_date_time_obj)) AS count_per_day,
        (SELECT COUNT(*)
         FROM tbl_registration_data rd2
         WHERE rd2.registration_date_time_obj <= date_add(date(rd.registration_date_time_obj), interval 1 day)
 FROM  tbl_registration_data rd
 GROUP BY day
 ORDER BY day;