How to select table row values as columns of sum of values

Question

I have 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-11-16','toy',5);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-11-19','santa',2);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-11-24','tree',5);
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 would like to see the sum of quantities having the dates per month one per row and the products names as columns, so I've created a query like:

SELECT DATE_FORMAT(`date`, '%Y-%m') As Date, 
IF(`productname` = 'santa', SUM(`quantity`), 'none') As santa,
IF(`productname` = 'toy', SUM(`quantity`), 'none') As toy,
IF(`productname` = 'tree', SUM(`quantity`), 'none') As tree  

FROM `products`

GROUP BY DATE_FORMAT(`date`, '%Y-%m'),`productname`

Which gives me something like this:

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

Which is almost nice, but I would like it to be like this, so only one row for a particular month:

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

Is it possible to achieve with queries?

Gurwinder Singh · Accepted Answer · 2016-12-24T10:35:16.733

2

This should do

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`
GROUP BY DATE_FORMAT(`date`, '%Y-%m');

edited Dec 24 '16 at 10:35

answered Dec 24 '16 at 10:24

Gurwinder Singh

38,557
6
51
76

Thank you! Also for the fast response :) – xedemx Dec 24 '16 at 12:07
I'd like to create a list of months for a given interval for the above scenario even if there is no data for a given month. Let' say in November there is no data at all. Still I'd like to have November in the query result with zeroes, would that be possible? Like (interval 2016-10 to 2016-12) : `| Date | santa | toy | tree | +---------+-------+------+------+ | 2016-10 | 50 | 50 | 50 | +---------+-------+------+------+ | 2016-11 | 0 | 0 | 0 | +---------+-------+------+------+ | 2016-12 | 8 | 5 | 15 |` @GurV – xedemx Feb 25 '17 at 08:30
@xedemx - Please don't keep asking new question on already answered ones. Accept the answer for this one if it answered the original question and ask a new one if you have any. – Gurwinder Singh Feb 25 '17 at 08:37
okay, I have created a new question here: http://stackoverflow.com/questions/42453963/how-to-display-all-months-in-pivot-querys-results-even-if-no-data-for-given-mon thank you @GurV – xedemx Feb 25 '17 at 08:57

score 1 · Answer 2 · answered Dec 24 '16 at 10:29

It looks like you want to do a pivot query on the product name. Try this:

SELECT DATE_FORMAT(date, '%Y-%m') AS Date, 
       COALESCE(SUM(CASE WHEN productname = 'santa'
                         THEN quantity END), 'none') AS santa,
       COALESCE(SUM(CASE WHEN productname = 'toy'
                         THEN quantity END), 'none') AS toy,
       COALESCE(SUM(CASE WHEN productname = 'tree'
                         THEN quantity END), 'none') AS tree,
FROM products
GROUP BY DATE_FORMAT(date, '%Y-%m')

Thank you! Indeed I was looking for a pivot. – xedemx Dec 24 '16 at 12:11 — xedemx, Dec 24 '16 at 12:11

How to select table row values as columns of sum of values

2 Answers2