CODEWITHSUNDEEP
mysqlsqldatabase
Marcus Christiansen
Marcus Christiansen

Reputation: 3197

MySQL Rolling Count of Rows

I'm trying to do a rolling count of registration growth on a student website.

The query looks as follows:

SELECT COUNT(type) as student_count, MONTH(created_at) as month, YEAR(created_at) as year
FROM users
WHERE type = 'student'
GROUP BY MONTH(created_at), YEAR(created_at)
ORDER BY year, month

This produces the following output: enter image description here

What I'm trying to achieve in the query is to keep adding up the student_counts from the previous rows.

So:

December 2014 should have 15 students

January 2015 should have 16 students

February 2015 should have 34 students

and so on...

Is this possible in SQL or is it better to do this when outputting the data in the code itself?

Upvotes: 2

Views: 604

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

One approach to handle this in MySQL uses a correlated subquery to find the running total.

SELECT DISTINCT
    (SELECT COUNT(*) FROM users u2
     WHERE DATE_FORMAT(u2.created_at, '%Y-%m') <=
           DATE_FORMAT(u1.created_at, '%Y-%m')) AS student_count,
    DATE_FORMAT(created_at, '%Y-%m') AS ym
FROM users u1
WHERE type = 'student'
ORDER BY DATE_FORMAT(created_at, '%Y-%m');

Demo

Not much to explain here, except that SELECT DISTINCT gives us each unique year-month value in the table as a single record. We then count all rows at that point in time or earlier to find the running total.

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37347

Try this:

SELECT @cumulative := 0;
SELECT @cumulative := @cumulative + student_count student_count,
       month, year
FROM (
    SELECT COUNT(type) as student_count,
           MONTH(created_at) as month,
           YEAR(created_at) as year
    FROM users
    WHERE type = 'student'
    GROUP BY MONTH(created_at), YEAR(created_at)
) A ORDER BY year, month

Upvotes: 0

juergen d
juergen d

Reputation: 204756

select *, @sum := @sum + student_count as sum
from
(
    SELECT YEAR(created_at) as year, 
           MONTH(created_at) as month, 
           COUNT(type) as student_count
    FROM users
    WHERE type = 'student'
    GROUP BY year, month
    ORDER BY year, month
) tmp
CROSS JOIN (select @sum := 0) s

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

Try using with rollup 

SELECT COUNT(type) as student_count, MONTH(created_at) as month, YEAR(created_at) as year
FROM users
WHERE type = 'student'
GROUP BY YEAR(created_at), MONTH(created_at) WITH ROLLUP

Upvotes: 0

Related Questions