SQL: how to get sum() of counted values

Question

I'm using the following statement to read out user-registrations

SELECT DAY(p.creationInstant) as day, MONTH(p.creationInstant) as month, 
       YEAR(p.creationInstant) as year, count(p.id) as users 
FROM person p WHERE p.enrollmentStatus ="Enrolled" 
GROUP BY year, month, day 
ORDER BY year, month, day

This will give me the following output:

day month year users
  1     1 2013     3
  2     1 2013     5
  3     1 2013     7
...

Now I'd like to have a 4th column that sums up the users:

day month year users **totalUsers**
  1     1 2013     3          3
  2     1 2013     5          8
  3     1 2013     7         15
...

But somehow I can't figure it out how to do it with SQL (dbms: mySQL).

Have a look at running totals, e.g. http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql — StuartLC, Sep 23 '13 at 10:45
Also useful reading: http://stackoverflow.com/questions/15101941/count-number-of-distinct-rows-for-multiple-values/15102524#15102524 — mavrosxristoforos, Sep 23 '13 at 10:50
"running totals" that would have been the search term! geez... sometimes I which english is my motherlanguage... thank you @StuartLC — renato, Sep 23 '13 at 10:53

score 0 · Accepted Answer · answered Sep 23 '13 at 11:10

So that's how my statement looks after the hint to the 'running totals' question and it works like a charm:

SET @runtot:=0;
SELECT q1.day, q1.month, q1.year, q1.users, 
       (@runtot := @runtot + q1.users) AS totalUsers
FROM (
  SELECT DAY(p.creationInstant) as day, MONTH(p.creationInstant) as month,
         YEAR(p.creationInstant) as year, count(p.id) as users 
  FROM PERSON p where p.enrollmentStatus ="Enrolled" 
  GROUP BY year, month, day 
  ORDER BY year, month, day) as q1

score 0 · Answer 2 · answered Sep 23 '13 at 11:23

select day,
       month,
       year,
       (SELECT sum(a.id)
          FROM person a
         WHERE a.enrollmentStatus = "Enrolled"
           and DAY(a.creationInstant) <= b.day)
  from (SELECT DAY(p.creationInstant) as day,
               MONTH(p.creationInstant) as month,
               YEAR(p.creationInstant) as year,
               count(p.id) as users
          FROM person p
         WHERE p.enrollmentStatus = "Enrolled"
         GROUP BY year, month, day
         ORDER BY year, month, day) b

SQL: how to get sum() of counted values

2 Answers2