MySQL works with COUNT

I know the method set @running_sum=0; @running_sum: =@running _sum + ... , however it doesn't seem to work in my case.

My request:

 SELECT DISTINCT(date), COUNT(*) AS count FROM table1 WHERE date > '2011-09-29' AND applicationid = '123' GROUP BY date ORDER BY date 

The result gives me unique dates with a count of occurrences of application 123.

I want to save the current amount of count to see the accumulated growth.

Now I am doing this in PHP, but I want to switch everything to MySQL.

Using the method from the first line of this message simply duplicates the account, and does not accumulate it.

What am I missing?

PS The set is very small, only about 100 entries.

Edit: you're right ypercube:

Here is the version with run_sum:

 SET @running_sum=0; SELECT date, @running_sum: =@running _sum + COUNT(*) AS total FROM table1 WHERE date > '2011-09-29' AND applicationid = '123' GROUP BY date ORDER BY date 

count count ends the same as if I just typed COUNT (*)

+7
source share
3 answers

Updated Answer

The OP requested a single-request approach, so as not to set the SET user variable separately from using the variable to calculate the current amount:

 SELECT d.date, @running_sum: =@running _sum + d.count AS running FROM ( SELECT date, COUNT(*) AS `count` FROM table1 WHERE date > '2011-09-29' AND applicationid = '123' GROUP BY date ORDER BY date ) d JOIN (SELECT @running_sum := 0 AS dummy) dummy; 

Inline initialization of user variables is useful for modeling other analytic functions. Indeed, I learned this technique from answers like this .

Original answer

You need to enter a closed query to tab @running_sum on your COUNT (*) ed entries:

 SET @running_sum=0; SELECT d.date, @running_sum: =@running _sum + d.count AS running FROM ( SELECT date, COUNT(*) AS `count` FROM table1 WHERE date > '2011-09-29' AND applicationid = '123' GROUP BY date ORDER BY date ) d; 

See also this answer .

+11
source

SQL, as you know, does not work well with totals. Since your result set is fine, you would be much better advised to add a calculated working column on the client side. Nothing in SQL will be as effective as this.

+2
source

The total execution amount can be easily calculated using the UDF lib_mysqludf_ta library.

https://github.com/mysqludf/lib_mysqludf_ta#readme

+1
source

All Articles