MYSQL query - getting totals by month and show zero total

Question

I have like this table

 MySQL> select * from order;
    +-----+-------------+---------------+
    | id  | order_total |  payment_date |
    +-----+-------------+---------------+
    |   1 |          35 |    2015-05-17 |
    |   2 |          35 |    2015-03-17 |
    |   3 |          65 |    2015-02-04 |
    |   4 |          39 |    2015-01-01 |
    |   5 |          19 |    2015-01-21 |
    |   6 |          27 |    2015-10-21 |
    |   7 |          74 |    2015-02-15 |
    |   8 |          81 |    2015-09-13 |
    |   9 |          86 |    2015-03-01 |
    |  10 |          42 |    2015-07-08 |
    |  11 |          73 |    2015-05-19 |
    |  12 |          78 |    2015-07-20 |
    |  13 |          92 |    2015-02-04 |
    ......
    |1913 |          71 |    2015-11-04 |
    +-----+-------------+---------------+

I need the result to show me each month payment for year 2015 I try to use

SELECT MONTHNAME(payment_date), 
       SUM(order_total) 
FROM order 
GROUP BY YEAR(payment_date), 
         MONTH(payment_date)

but its not giving the zero months I read this question also but he have tow table I need the result to show me each month payment with total like this

+-----+-------+
| MM  | Total | 
+-----+-------+
|  01 |  5735 |
|  02 |  2535 | 
|  03 |  1065 | 
|  04 |     0 | 
|  05 |   919 | 
|  06 |     0 | 
|  07 |  2874 | 
|  08 |     0 | 
|  09 |  6086 | 
|  10 |  2042 | 
|  11 |  1973 | 
|  12 |     0 | 
+-----+-------+

You need to have a dates or months table and then you can use that with an `outer join`... — sgeddes, May 25 '16 at 18:54

score 1 · Answer 1 · answered May 25 '16 at 20:24

There needs to be a row source for the months, the twelve rows you need returned. There's several possible ways to do that. One approach is to use an inline view to return the twelve rows, without creating a table.

For example:

 SELECT i.mm                         AS `mm`
      , IFNULL(SUM(o.order_total),0) AS `total`
   FROM ( SELECT '01' AS mm
          UNION ALL SELECT '02'
          UNION ALL SELECT '03'
          UNION ALL SELECT '04'
          UNION ALL SELECT '05'
          UNION ALL SELECT '06'
          UNION ALL SELECT '07'
          UNION ALL SELECT '08'
          UNION ALL SELECT '09'
          UNION ALL SELECT '10'
          UNION ALL SELECT '11'
          UNION ALL SELECT '12'
        ) i
   LEFT
   JOIN `order` o
     ON o.payment_date >= '2015-01-01' + INTERVAL ( i.mm - 1 ) MONTH 
    AND o.payment_date  < '2015-01-01' + INTERVAL ( i.mm + 0 ) MONTH
  GROUP BY i.mm

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

You need a month table with all the months. Check this answer

allMonths

  id, year, month

Then create a left join

 SELECT a.year, a.month,  COALESCE(SUM(o.order_total ),0) as total
 FROM allMonths a
 LEFT JOIN orders o
        ON a.year = YEAR(payment_date)
       AND a.month = MONTH(payment_date)
 GROUP BY a.year, 
          a.month

EDIT:

Also instead of use month and year you can always truncate the date to first day of the month

 SELECT DATE_FORMAT(payment_date, '%Y-%m-01');

In that case join condition is easy save month like yyyy-mm-01

   ON a.month = DATE_FORMAT(payment_date, '%Y-%m-01')

edited May 23 '17 at 12:22

Community

1
1

answered May 25 '16 at 18:55

Juan Carlos Oropeza

47,252
12
78
118

its working like cream I just add COALESCE(SUM(o.order_total ),0) thanks – Ayman Dasa May 25 '16 at 19:10
Yes forgot the `coalesce` – Juan Carlos Oropeza May 25 '16 at 19:16

MYSQL query - getting totals by month and show zero total

2 Answers2