MySQL aggregate by month with running total

Question

Possible Duplicate:
Calculate a running total in MySQL

I'm monitoring the number of users created since 2011 in an application by month for a chart using MySQL and PHP. As a part of the query I would also like to include a running total.

SELECT
  DATE_FORMAT(created,'%Y%m%d') as 'Date',
  COUNT(item_id) as 'NewUsers'
FROM AP_user
WHERE YEAR(created) > 2011
  AND user_groups = '63655'
  AND user_active = 1
  AND userID NOT IN $excludedUsers
GROUP BY MONTH(created) ASC

I'm able to return the "users by month" but how do I include a running total as a part of this query?

You'll need a subquery. Add it to your select and call it total. `SELECT (SELECT "get total here") as total` Or in PHP just add up the total for each month. — Farzher, Aug 22 '12 at 16:36
I added the following subquery and MySQL doesn't seem to like it: `SELECT (SELECT COUNT(item_id) FROM AP_user WHERE YEAR(created) > 2011 AND user_groups = '63655' AND user_active = 1 AND userID NOT IN $excludedUsers) as 'TotalUsers' DATE_FORMAT(created,'%Y%m%d') as 'Date', COUNT(item_id) as 'NewUsers' FROM AP_user WHERE YEAR(created) > 2011 AND user_groups = '63655' AND user_active = 1 AND userID NOT IN $excludedUsers GROUP BY MONTH(created) ASC` — Bill, Aug 22 '12 at 16:54
@StephenSarcsamKamenar post it as an answer, so Bill can accept it and get reputation ;) — Gonzalo.-, Aug 22 '12 at 16:59
This, however, only gives me the overall total and I need a _running_ total. — Bill, Aug 22 '12 at 16:59
It sounds to me like for each month, he also wants the total of all users created in total as of the end of that month. See this question and the accepted answer: http://stackoverflow.com/questions/8691201/accumulated-sum-in-query — Nate C-K, Aug 22 '12 at 17:21
IMO that question is more or less a duplicate of yours, just with a different aggregate function (sum instead of count). — Nate C-K, Aug 22 '12 at 17:26
I guess it looks similar but I'm having a hard time translating it to my circumstances. I came up with the following to no avail: `SELECT DATE_FORMAT(created,'%Y%m%d') as 'Date', COUNT(user_id) as 'NewUsers', (SELECT created, COUNT(user_id), @running_count := @running_count + COUNT(item_id) as Counter FROM AP_user, (SELECT @runing_count := 0 ) as T1 ORDER BY COUNT(item_id)) as 'TotalUsers' FROM AP_user WHERE YEAR(created) > 2011 AND user_groups = '63655' AND user_active = 1 GROUP BY MONTH(created) ASC` — Bill, Aug 22 '12 at 18:15
I'm getting a "#1241 - Operand should contain 1 column(s)" that appears to be associated with my second SELECT statement but if I remove the created and user_id fields my running total shows NULL. — Bill, Aug 22 '12 at 18:43
Nice Mr @Bill. I have never needed it. I am new to stack-overflow but pretty much familiar with sql queries. Your idea is impressive. I am trying to make it. But your question has become my first favorite question on stack overflow — Sami, Aug 22 '12 at 19:01
It will be easier to implement in your language for GUI/Coding. Are you using any? Are you in favor to do it other simpler way or only here in sql query? — Sami, Aug 22 '12 at 19:05

spencer7593 · Accepted Answer · 2012-08-22T21:09:58.557

Unfortunately, MySQL doesn't provide analytic functions, like Oracle and SQL Server do.

One way to get a "running total" is to make use of a user variable, something like this:

  SELECT t.Date
       , t.NewUsers
       , @rt := @rt + t.NewUsers AS `Running Total`
    FROM (SELECT @rt := 0) i
    JOIN (
           SELECT DATE_FORMAT(created,'%Y%m%d') AS `Date`
                , COUNT(item_id) as `NewUsers`
             FROM AP_user
            WHERE YEAR(created) > 2011
              AND user_groups = '63655'
              AND user_active = 1
              AND userID NOT IN $excludedUsers
            GROUP BY DATE_FORMAT(created,'%Y-%m')
            ORDER BY DATE_FORMAT(created,'%Y-%m') ASC
         ) t

NOTE: The behavior of memory variables like used above is not guaranteed in this context. But if we are careful with the query, we can get predictable, repeatable results in SELECT statements. The behavior of memory variables may change in a future release, rendering this approach unworkable.

NOTE: I basically wrapped your query in parentheses, and gave it an alias as an inline view (what MySQL calls a "derived table"). I made a few changes to your query, your GROUP BY has potential to group January 2012 together with January from 2013, I changed that. I also added an ORDER BY clause.

Need "," after "created" in the Order and Group By sections but other than that it works great, thanks! — Bill, Aug 22 '12 at 21:02
Due to complex nature of question's requirement answer is tough to understand. Yet great work @spencer7593. thanks to question as well. I have voted for both. I will use it in future. I was not expecting it solvable in a single query — Sami, Aug 23 '12 at 15:08
@Sami Akram: you are correct, it's not really solvable in a "single query", in that my solution is basically forcing MySQL to run a subquery to materialize an inline view (or a "derived table" in MysQL lingo), and then the outer query runs against that. We can get it all done in a single SELECT, but we are (effectively) causing MySQL to run two queries for us. — spencer7593, Aug 23 '12 at 15:19

MySQL aggregate by month with running total

1 Answers1

Linked