mysql sql: how to cumulate count ? (sum of count)

Question

My goal is to get cumulate number of users registered by date

Here is my mysql sql

SELECT MONTH( DATE ) AS `month`, COUNT(userid) 
FROM  `stats` 
WHERE  `userid` = 1
GROUP BY `month`

This gives me number of users per month, but does not cumulate them

result:

month 1 : 90
month 2 : 50 (it should be 90 + 50)
month 3 : 10 (it should be 90 + 50 + 10)

I tried:

SELECT month,
       SUM( CNT ) AS CUM_CNT_TILL_NOW
  FROM  (
        SELECT MONTH( DATE ) AS `month`, COUNT(userid) AS CNT
          FROM  `stats` 
         WHERE  `userid` = 1
         GROUP BY `month`
      );

and got error: #1248 - Every derived table must have its own alias

I don't think the aforementioned duplicate will help this OP because it doesn't involve aggregation. — Gordon Linoff, Oct 01 '16 at 22:33

score 2 · Accepted Answer · answered Oct 01 '16 at 22:36

In MySQL, there are basically three ways to do a cumulative sum:

A correlated subquery.
Inequality joins with aggregation.
Variables.

The latter is the simplest. However, because of the way that group by works in MySQL, often you need a subquery:

SELECT yyyy, mm, cnt,
       (@sum := @sum + cnt) as cume_sum
FROM (SELECT YEAR(DATE) as yyyy, MONTH( DATE ) AS mm, COUNT(userid) AS CNT
      FROM stats
      WHERE userid = 1
      GROUP BY yyyy, mm
     ) ym CROSS JOIN
     (SELECT @sum := 0) params
ORDER BY yyyy, mm;

Notes:

This wisely takes the year into account. That is usually intended when you are accumulating by month.
The @sum variable is defined in the query. This is a convenience.
The subquery is needed because sometimes variables do not work as expected with aggregations.
The subquery has an alias.

Teja · Answer 2 · 2016-10-01T22:09:38.153

0

SELECT month,
       SUM( CNT ) OVER ( ORDER BY month ROWS BETWEEN UNBOUNDED PRECEEDING  
                         AND CURRENT ROW 
                        ) AS CUM_CNT_TILL_NOW
  FROM
     (
        SELECT MONTH( DATE ) AS `month`, COUNT(userid) AS CNT
          FROM  `stats` 
         WHERE  `userid` = 1
         GROUP BY `month`
      );

Another Solution :-

WITH tmp AS 
(
  SELECT MONTH( DATE ) AS `month`, COUNT(userid) AS CNT 
    FROM  `stats` 
   WHERE  `userid` = 1
  GROUP BY `month`
 )
SELECT o.month, o.cnt , RunningTotal = o.cnt + COALESCE(
(
  SELECT SUM(cnt) AS cnt
    FROM tmp i
    WHERE i.month < o.month), 0
)
FROM tmp AS o
ORDER BY o.month;

edited Oct 01 '16 at 22:09

answered Oct 01 '16 at 21:26

Teja

13,214
36
93
155

what is the : ( ORDER BY month ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW ) ??? is that a comment ? – yarek Oct 01 '16 at 21:33
ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW is a sql syntax where you can perform computation on all the rows before current row. – Teja Oct 01 '16 at 21:37
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ORDER BY month ROWS BETWEEN UNBOUNDED PRECEEDING AN' at line 2 – yarek Oct 01 '16 at 21:37
BTW, I am using MYSQL – yarek Oct 01 '16 at 21:39
I added another solution using correlated query. Try this. – Teja Oct 01 '16 at 21:57

score 0 · Answer 3 · answered Oct 01 '16 at 21:50

In Mysql, you can make use of session variable. like below:

SET @sum = 0;
SELECT MONTH( DATE ) AS `month`, @sum:=@sum+COUNT(userid) as sum
FROM  `stats` 
WHERE  `userid` = 1
GROUP BY `month`;

Or else, You will have to join the two tables but that would be inefficient.

dy'sback · Answer 4 · 2016-10-01T22:19:42.717

0

try with something like:

SELECT MONTH(DATE) AS `month`, COUNT(userid) , (SELECT COUNT(userid) 
    FROM  `stats` 
    WHERE  `userid` = 1 AND MONTH(date) <= MONTH(s.date))
FROM  `stats` s
WHERE  `userid` = 1
GROUP BY `month`

Thanks jpw for correction

edited Oct 01 '16 at 22:19

answered Oct 01 '16 at 22:05

dy'sback

115
5

1

If you change the `where` clause in the subquery to `WHERE userid = 1 AND MONTH(DATE) <= MONTH(s.DATE)` your query should produce the expected result. – jpw Oct 01 '16 at 22:15

mysql sql: how to cumulate count ? (sum of count)

4 Answers4