CODEWITHSUNDEEP
mysqlgroup-bycumulative-sum
Ryan
Ryan

Reputation: 531

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

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

Upvotes: 1

Views: 1170

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270281

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.

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

Upvotes: 1

Related Questions