MySQL: Calculate a Running Total Using a GROUP BY Using Ordered Month

Question

I'm attempting to make a running total using months. Here's a simplified version of what I'm doing that partially works:

SELECT 
   MONTHNAME(STR_TO_DATE(DateContacted,'%m/%d/%Y')) AS MONTH, 
   IFNULL(COUNT(CASE WHEN s.surveyid = 649814 THEN s.id ELSE NULL END),0) Signer,
   (SELECT COUNT(s2.id) FROM sq s2
       WHERE s2.surveyid = 649814 AND MONTHNAME(STR_TO_DATE(s2.DateContacted,'%m/%d/%Y')) <= MONTH
       GROUP BY MONTH
       ORDER BY MONTH) RT
FROM  
  sq s
GROUP  BY MONTH
ORDER  BY MONTH

But the problem is that the order is off, which is obviously a big deal when you're trying to do a running total:

MONTH     Signer RT
April     1646  1646
August    81    1727
December  0     1727
February  0     1727
January   0     1727
July      24    1751
June      241   1992
March     2120  4112
May       115   4227
November  28    4255
October   173   4428
September 73    4501

But when I attempt to order it properly like this:

SELECT 
   MONTHNAME(STR_TO_DATE(DateContacted,'%m/%d/%Y')) AS MONTH, 
   IFNULL(COUNT(CASE WHEN s.surveyid = 649814 THEN s.id ELSE NULL END),0) Signer,
   (SELECT COUNT(s2.id) FROM sq s2
       WHERE s2.surveyid = 649814 AND MONTHNAME(STR_TO_DATE(s2.DateContacted,'%m/%d/%Y')) <= MONTH
       GROUP BY MONTH
       ORDER BY FIELD(MONTH,'January','February','March','April','May','June','July','August','September','October','November','December')) RT
FROM  
  sq s
GROUP BY MONTH
ORDER BY FIELD(MONTH,'January','February','March','April','May','June','July','August','September','October','November','December')

It throws off all the RT counts (though the Signer counts are right):

MONTH     Signer RT
January   0     1727
February  0     1727
March     2120  4112
April     1646  1646
May       115   4227
June      241   1992
July      24    1751
August    81    1727
September 73    4501
October   173   4428
November  28    4255
December  0     1727

I used this method instead of a subquery because this is normally part of a much larger query where I LEFT JOIN two tables together by month. I also seemingly can't use the @runtot variable solution unfortunately because this needs to be GROUPed by month at the end and that throws it off. I know MySQL's not perfect for this kind of functions but I seem to be close and if this could be along with the rest of my query in the same place, that'd be great. Any help is appreciated!

Edit:

Here's the ideal end-table:

MONTH     Signer RT
January   0      0
February  0      0
March     2120   2120
April     1646   3766
May       115    3881
June      241    4122
July      24     4146
August    81     4227
September 73     4300
October   173    4473
November  28     4501
December  0      4501

Sorry I dont see the error on your second query, All the values match the first one — Juan Carlos Oropeza, Nov 24 '15 at 23:39
@JuanCarlosOropeza Since the 'Signer' column is right, the running total (RT) column in the second query should be 0 for January, 0 for February, 2120 for March, 3766 for April, 3881 for May, etc. — Ryan, Nov 24 '15 at 23:42
If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Nov 24 '15 at 23:44
@JuanCarlosOropeza The first query's also wrong -- though it's technically doing what it should be doing if the months were in alphabetical order. But since they're not, the only way I could get them to order properly was by using `ORDER BY FIELD` -- which works in general for this stuff but throws off the running totals. — Ryan, Nov 24 '15 at 23:44
@Strawberry Thanks for the advice! I have a couple meetings now so I can't create the schema or SQL Fiddle for this yet but I did add the desired final table as the outcome if that helps for now. I'll add additional information in a bit if it's still not clear. — Ryan, Nov 24 '15 at 23:49

Juan Carlos Oropeza · Accepted Answer · 2015-11-25T00:33:00.603

Can you add the aditional field monthid?

SELECT
   MONTH(STR_TO_DATE(DateContacted,'%m/%d/%Y')) AS MONTHID,  
   MONTHNAME(STR_TO_DATE(DateContacted,'%m/%d/%Y')) AS MONTH, 
   IFNULL(COUNT(CASE WHEN s.surveyid = 649814 THEN s.id ELSE NULL END),0) Signer,
   (SELECT COUNT(s2.id) 
    FROM sq s2
    WHERE s2.surveyid = 649814 
      AND MONTH(STR_TO_DATE(s2.DateContacted,'%m/%d/%Y')) <= MONTHID
    GROUP BY MONTHID, MONTH
    ORDER BY MONTHID) RT
FROM  
  sq s
GROUP  BY MONTHID, MONTH
ORDER  BY MONTHID

I just create my own sample to test other option without need to add another field.

SQL Fiddle Demo

SELECT MONTHNAME(`date`)  as MONTH,
       IFNULL(COUNT(CASE WHEN `sales` % 3 = 0 THEN 1 ELSE NULL END),0) as SIGNER,
       (SELECT COUNT(s2.`sales`)
        FROM Table1 s2
        WHERE
             s2.`sales` % 3 = 0
        AND  MONTH(`date`)  <= MONTH(s.`date`)
        GROUP BY MONTH
        ORDER BY MONTH) RT
FROM  
  Table1 s
GROUP  BY MONTH(`date`)
ORDER  BY MONTH(`date`)

OUTPUT

|    MONTH | SIGNER | RT |
|----------|--------|----|
|  January |      2 |  2 |
| February |      3 |  5 |
|    March |      2 |  7 |
|    April |      2 |  9 |
|      May |      1 | 10 |
|     June |      1 | 11 |
|     July |      2 | 13 |
|   August |      1 | 14 |
| December |      1 | 15 |

NOTE:

After finish the sample I realize my december is from 2014 and the rest are 2015. So be carefull you are filtering YEAR = 2015

score 1 · Answer 2 · answered Nov 25 '15 at 01:01

There is no reason to use order by in the subquery. Nor to have a complicated order by. Instead:

SELECT MONTHNAME(STR_TO_DATE(DateContacted, '%m/%d/%Y')) AS MONTH, 
       SUM(s.surveyid = 649814) as Signer,
       (SELECT COUNT(s2.id)
        FROM sq s2
        WHERE s2.surveyid = 649814 AND 
              DATE_FORMAT(STR_TO_DATE(s2.DateContacted, '%m/%d/%Y'), '%Y-%m') <= DATE_FORMAT(STR_TO_DATE(s.DateContacted, '%m/%d/%Y'), '%Y-%m')
       )
FROM sq s
GROUP BY DATE_FORMAT(STR_TO_DATE(s.DateContacted, '%m/%d/%Y'), '%Y-%m')
ORDER BY MIN(STR_TO_DATE(DateContacted, '%m/%d/%Y'));

Notes:

Storing a date as a string is a bad idea.
Storing a date as a string in any format other than YYYYMMDD or YYYY-MM-DD is an even worse idea.
Calling such a column a "date" is really, really bad.
In any case, the ORDER BY does not need some complicated list of month names. You can simply use any date during the month (and MIN() is convenient).
Similarly, the monthly comparisons and aggregation can just use YYYY-MM formats.
The subquery should have neither a GROUP BY nor an ORDER BY.

Dont know why I got a +1 three years later, but this answer is much better than mine. — Juan Carlos Oropeza, Apr 05 '18 at 18:19

MySQL: Calculate a Running Total Using a GROUP BY Using Ordered Month

2 Answers2