yarek yarek - 2 months ago 17
MySQL Question

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

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

Answer

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.