CODEWITHSUNDEEP
mysqldatecountgroup-bysum
lickmycode
lickmycode

Reputation: 2079

Get total of rows in a mysql table grouped by months and years

I'd like to create some statistics and I want to them in an array grouped by months and years.

Got an MySQL table_users with column user_id. I want build up a mysql query to list how many members we had in total at the end of each month. The result of the query should be:

 Year:   Month:    Members:
 --------------------------
 2014     12        11345
 2015      1        17939
 2015      2        25003
 2015      3        32667

There is also the column user_signupdate with the UNIX timestamp when the user_id was added. Whatever I've tried so far, I'm getting only the growing of user_id's, but I'd like to get the total of all user_id's we had for each month and year.

Is it possible to count and group this with only one MySQL query?

Upvotes: 0

Views: 705

Answers (1)

WorkSmarter
WorkSmarter

Reputation: 3808

The following code will perform simple arithmetic calculation to generate the members running total. See SQL Fiddle demo.

select 
t1.year,
t1.month,
(@rtotal := @rtotal + t1.members) AS members
from 
(select year(user_signupdate) as year, month(user_signupdate) as month, count(user_id) as members
from table_users
group by year(user_signupdate), month(user_signupdate)
order by year(user_signupdate), month(user_signupdate)) as t1, (Select @rtotal:=0) as rt

Upvotes: 1

Related Questions