djdy djdy - 2 months ago 24
MySQL Question

MySQL Running Total with COUNT

I'm aware of the

set @running_sum=0;
@running_sum:=@running_sum + ...
method, however, it does not seem to be working in my case.

My query:

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 the count of occurrences of application 123.

I want to keep a running total of the
count
, to see the accumulated growth.

Right now I'm doing this in PHP, but I want to switch it all to MySQL.

Using the method from the first line of this post simply duplicates the count, instead of accumulating it.

What am I missing?

P.S. The set is very small, only about 100 entries.

Edit: you're right ypercube:

Here's the version with running_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 column ends up being the same as if I just printed COUNT(*)

Answer

Updated Answer

The OP asked for a single-query approach, so as not to have to SET a user variable separately from using the variable to compute the running total:

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 simulating other analytic functions, too. Indeed I learned this technique from answers like this one.

Original Answer

You need to introduce an enclosing query to tabulate the @running_sum over your COUNT(*)ed records:

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.