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
– Gordon Linoff, Commented Oct 1, 2016 at 22:33

Gordon Linoff · Accepted Answer · 2016-10-01 22:36:10Z

2

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.

answered Oct 1, 2016 at 22:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kanchan · Accepted Answer · 2016-10-01 21:50:25Z

0

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.

answered Oct 1, 2016 at 21:50

kanchan

3591 gold badge4 silver badges15 bronze badges

Comments

Teja · Accepted Answer · 2016-10-01 22:09:38Z

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 1, 2016 at 22:09

answered Oct 1, 2016 at 21:26

Teja

13.7k38 gold badges103 silver badges164 bronze badges

5 Comments

yarek Over a year ago

what is the : ( ORDER BY month ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW ) ??? is that a comment ?

Teja Over a year ago

ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW is a sql syntax where you can perform computation on all the rows before current row.

yarek Over a year ago

#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 Over a year ago

BTW, I am using MYSQL

Teja Over a year ago

I added another solution using correlated query. Try this.

dy'sback · Accepted Answer · 2016-10-01 22:19:42Z

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 1, 2016 at 22:19

answered Oct 1, 2016 at 22:05

dy'sback

1247 bronze badges

1 Comment

jpw Over a year ago

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.

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related