[MySQL] How to insert additional values in between a GROUP BY

Question

Hi, i am currently making a monthly report using MySQL. I have a table named "monthly" that looks something like this:

  id |         date        | amount

---

  10 | 2009-12-01 22:10:08 | 7 
  9  | 2009-11-01 22:10:08 | 78 
  8  | 2009-10-01 23:10:08 | 5 
  7  | 2009-07-01 21:10:08 | 54 
  6  | 2009-03-01 04:10:08 | 3 
  5  | 2009-02-01 09:10:08 | 456 
  4  | 2009-02-01 14:10:08 | 4 
  3  | 2009-01-01 20:10:08 | 20 
  2  | 2009-01-01 13:10:15 | 10 
  1  | 2008-12-01 10:10:10 | 5

Then, when i make a monthly report (which is based by per month of per year), i get something like this.

yearmonth | total

2008-12   | 5 
2009-01   | 30 
2009-02   | 460 
2009-03   | 3 
2009-07   | 54 
2009-10   | 5 
2009-11   | 78 
2009-12   | 7

I used this query to achieved the result:

SELECT substring( date, 1, 7 ) AS yearmonth, sum( amount ) AS total FROM monthly GROUP BY substring( date, 1, 7 )

But I need something like this:

yearmonth | total

2008-01   | 0 
2008-02   | 0 
2008-03   | 0 
2008-04   | 0 
2008-05   | 0 
2008-06   | 0 
2008-07   | 0 
2008-08   | 0 
2008-09   | 0
2008-10   | 0 
2008-11   | 0 
2008-12   | 5 
2009-01   | 30 
2009-02   | 460 
2009-03   | 3 
2009-05   | 0
2009-06   | 0
2009-07   | 54 
2009-08   | 0
2009-09   | 0
2009-10   | 5 
2009-11   | 78 
2009-12   | 7

Something that would display the zeroes for the month that doesnt have any value. Is it even possible to do that in a MySQL query?

Answer 1

You should generate a dummy rowsource and LEFT JOIN with it:

SELECT  *
FROM    (
        SELECT  1 AS month
        UNION ALL
        SELECT  2
        … 
        UNION ALL
        SELECT  12 
        ) months
CROSS JOIN
        (
        SELECT  2008 AS year
        UNION ALL
        SELECT  2009 AS year
        ) years
LEFT JOIN
        mydata m
ON      m.date >= CONCAT_WS('.', year, month, 1)
        AND m.date < CONCAT_WS('.', year, month, 1) + INTERVAL 1 MONTH
GROUP BY
        year, month

You can create these as tables on disk rather than generate them each time.

MySQL is the only system of the major four that does have allow an easy way to generate arbitrary resultsets.

Oracle, SQL Server and PostgreSQL do have those (CONNECT BY, recursive CTE's and generate_series, respectively)

Answer 2

Quassnoi is right, and I'll add a comment about how to recognize when you need something like this:

You want '2008-01' in your result, yet nothing in the source table has a date in January, 2008. Result sets have to come from the tables you query, so the obvious conclusion is that you need an additional table - one that contains each month you want as part of your result.