How to get sum of previous year's total with the next year total

Question

I have a below query

SELECT COUNT( DISTINCT id ) AS `total` , YEAR( created_date ) AS `memberYear`
FROM `users` AS `u`
WHERE (
role_id =2
AND is_deleted =0
AND is_trashed =0
)
GROUP BY `memberYear`

With this query i am getting record like this

total   memberYear
 10        2012 
 12        2013 
 2         2014

Now i need to get record total sum with previous year

total   memberYear sumTotal
10        2012       10
12        2013       22
2         2014       24

How can i get this sum of Total ? Any help will be appreciated.

The COUNT is returning you the total number of users for that perticular year... what do you want to achieve... — justrohu, Sep 22 '14 at 12:22
Suppose i have count of users in 2012 is 10, Now in year 2013 count of user is 12. So in 2013 total should be 10+12 = 22. I want to get this kind of total — Nikul, Sep 22 '14 at 12:24
This could probably done in one query but I don't know how *not this early in the morning; I need a 2nd cup of coffee for it*. Off the top of my head, you could do it in PHP and do two queries, by merely adding them both up. `$total = $query1 + $query2;` — Funk Forty Niner, Sep 22 '14 at 12:24
This is called a **running total**. It's not hard to out how to do this in MySQL. — O. Jones, Sep 22 '14 at 12:27

ovi · Answer 1 · 2014-09-22T12:33:50.673

1

You can only do that with mysql if you use a stored procedure, or use a nested select, but I would not recommend it. Since you use PHP, and already have the query response, just add the current year value to the previous one's :

$sumTotal=0;
while ($row=mysql_fetch_array($res)){
    $sumTotal+=$row["total"];
}

edited Sep 22 '14 at 12:33

answered Sep 22 '14 at 12:23

ovi

566
4
17

this seems pretty unlogical to me to be honest. – Azrael Sep 22 '14 at 12:26
It is more than logical to me. Why use a nested select, when you already have all that you need in the results ? – ovi Sep 22 '14 at 12:29
1

but the $sumTotal and $previousYearValue .. why in earth would you need both, you can do it by only using the $sumTotal value – Azrael Sep 22 '14 at 12:31
1

yes, that is true, I thought that he only needed the sum of the current and previous year. I changed the code, thank you @Azrael – ovi Sep 22 '14 at 12:33
okay, now you can have my upvote ;) – Azrael Sep 22 '14 at 12:36

score 1 · Accepted Answer · answered Sep 22 '14 at 12:28

Please have a try with this one:

SELECT COUNT( DISTINCT id ) AS `total` , YEAR( created_date ) AS `memberYear`,
@running_total := @running_total + COUNT(DISTINCT id) AS total
FROM `users` AS `u`
, (SELECT @running_total := 0) var_init_subquery
WHERE (
role_id =2
AND is_deleted =0
AND is_trashed =0
)
GROUP BY `memberYear`

score 0 · Answer 3 · answered Sep 22 '14 at 12:27

You are looking for the cumulative sum. Unfortunately mysql does not yet support window functions which makes such things real easy. One way is to use a theta self join like:

SELECT X.memberYear, sum(y.total)
FROM (
    SELECT COUNT( DISTINCT id ) AS `total` , YEAR( created_date ) AS `memberYear`
    FROM `users` AS u
    WHERE role_id =2
      AND is_deleted =0
      AND is_trashed =0
    GROUP BY memberYear
) AS x 
JOIN (
    SELECT COUNT( DISTINCT id ) AS `total` , YEAR( created_date ) AS `memberYear`
    FROM `users` AS `u`
    WHERE role_id =2
      AND is_deleted =0
      AND is_trashed =0
    GROUP BY `memberYear`
) y
   on y.memberyear <= x.memberyear
group by X.memberYear

This is untested, so there might be some errors in there.

How to get sum of previous year's total with the next year total

3 Answers3