How to display all months in pivot query's results even if no data for given month

Question

For the below table:

CREATE TABLE products
(
date DATE,
productname VARCHAR(80),
quantity INT(5)
);

INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-12-16','toy',5);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-12-18','santa',8);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-12-23','tree',15);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-01','toy',10);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-04','santa',20);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-09','tree',30);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-01','toy',40);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-04','santa',30);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-09','tree',20)

I have the following pivot query:

SELECT DATE_FORMAT(`date`, '%Y-%m') As Date,
  IFNULL(Sum(Case when `productname` = 'santa' then `quantity` end),0) As santa, 
  IFNULL(Sum(Case when `productname` = 'toy' then `quantity` end),0) As toy, 
  IFNULL(Sum(Case when `productname` = 'tree' then `quantity` end),0) As tree 
FROM `products`
WHERE `Date` BETWEEN '2016-10' and '2016-12'
GROUP BY DATE_FORMAT(`date`, '%Y-%m');

Which results:

+---------+-------+------+------+
| Date    | santa | toy  | tree |
+---------+-------+------+------+
| 2016-10 | 50    | 50   | 50   |
+---------+-------+------+------+
| 2016-12 | 8     | 5    | 15   |
+---------+-------+------+------+

Which works great, but I'd like to include all the months for a given interval even if there is no data for that month. So in the above example I'd like to have this, as a result:

+---------+-------+------+------+
| Date    | santa | toy  | tree |
+---------+-------+------+------+
| 2016-10 | 50    | 50   | 50   |
+---------+-------+------+------+
| 2016-11 | 0     | 0    | 0    |
+---------+-------+------+------+
| 2016-12 | 8     | 5    | 15   |
+---------+-------+------+------+

Is it possible with SQL?

Join to a table that has all the months. Or, if using MariaDB, use a [_sequence" table_](https://mariadb.com/kb/en/mariadb/sequence/) to generate all the months on the fly. — Rick James, Feb 25 '17 at 21:00

score 1 · Answer 1 · answered Feb 25 '17 at 12:21

The canonical way is to start with a list of months and use left join:

SELECT DATE_FORMAT(m.month_start, '%Y-%m') as yyyymm,
       Sum(Case when productname = 'santa' then quantity else 0 end) As santa, 
       Sum(Case when productname = 'toy' then quantity else 0 end) As toy, 
       Sum(Case when productname = 'tree' then quantity else 0 end) As tree 
FROM (SELECT DATE('2016-10-01') as month_start UNION ALL
      SELECT DATE('2016-11-01') as month_start UNION ALL
      SELECT DATE('2016-12-01') as month_start
     ) m LEFT JOIN
     products p
     ON p.Date >= m.month_start AND
        p.Date < m.month_start + interval 1 month
GROUP BY DATE_FORMAT(m.month_start, '%Y-%m')
ORDER BY yyyymm;

Notes:

There is no need to use back ticks, unless you really need them. The column and table names in your query do not require them.
This manually generates the list of months. You can also generate the list more easily if you have a calendar table.
Strictly speaking the ORDER BY is not necessary in MySQL, but the guarantee that that GROUP BY does a sort is deprecated behavior.
The SUM() cannot return NULL if you include an ELSE clause.

This is a duplicate as pointed out by Shadow, but nevertheless, thank you for your answer, it helped me to understand the problem better. - @Gordon Linoff — xedemx, Feb 25 '17 at 15:30

Gurwinder Singh · Answer 2 · 2017-02-25T09:28:21.180

You can use the cross join trick to generate range of dates and then left join your table with it and aggregate to get the desired results.

select c.ym, 
    coalesce(santa,0) santa,
    coalesce(toy,0) toy,
    coalesce(tree,0) tree
from (
SELECT DATE_FORMAT(`date`, '%Y-%m') As ym,
  IFNULL(Sum(Case when `productname` = 'santa' then `quantity` end),0) As santa, 
  IFNULL(Sum(Case when `productname` = 'toy' then `quantity` end),0) As toy, 
  IFNULL(Sum(Case when `productname` = 'tree' then `quantity` end),0) As tree 
FROM `products`
GROUP BY DATE_FORMAT(`date`, '%Y-%m')
) t right join (select ym
from (
    select date_format(str_to_date('2016-10-01','%Y-%m-%d') + INTERVAL (a.a + (10 * b.a)) MONTH,'%Y-%m') as ym
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
) a
where ym between '2016-10' and '2016-12'
) c on t.ym = c.ym;

Produces:

    Date    santa   toy tree
1   2016-10 50  50  50
2   2016-11 0   0   0
3   2016-12 8   5   15

Demo

This is a duplicate as pointed out by Shadow, but nevertheless, thank you for your answer, it helped me to understand the problem better. @GurV — xedemx, Feb 25 '17 at 15:30

How to display all months in pivot query's results even if no data for given month

2 Answers2

Demo

Linked