Mysql sales table grouped by customer and displayed by month columns pivot query

Question

I have a sales table with the following columns:

|  Customer_Id | amount |  date  |

What would be the best way to group data by customer_id, and displaying monthly total(SUM) of amount for each Customer_id (one row per Customer_id), on distinct month columns?

The desired output would be something like:

Customer     |January    | February   | March       | ....

Customer_id  |SUM amount | SUM amount | SUM amount | ....

I believe in Sql this is called a pivot table.

¡Thanks!

This is often done with the grouping as in most of the answers and then column that use `case` expressions. `sum(case when month(date) = 1 then amount else null end) as January, ...` — shawnt00, Feb 26 '15 at 05:00
Generally, for reasons of scalability and flexibility, issues of data display are best handled in application level code (e.g. PHP) if such a thing is available. — Strawberry, Feb 26 '15 at 07:17
@shawnt00 How could I use sum(case when mont... and have results displayed only ONE row per customer_id? — helloworld, Feb 26 '15 at 16:48
You probably didn't add a `GROUP BY Customer_id` which by definition gets you ONE row per customer_id. — shawnt00, Feb 26 '15 at 16:50

Piotr Olaszewski · Accepted Answer · 2015-02-26T18:00:35.230

Let's say you have following table:

mysql> select * from sales;
+-------------+--------+------------+
| customer_id | amount | date       |
+-------------+--------+------------+
|           1 |     12 | 2015-01-01 |
|           1 |      1 | 2015-01-02 |
|           1 |    663 | 2015-02-12 |
|           2 |     22 | 2015-01-03 |
|           2 |     21 | 2015-02-12 |
|           2 |     11 | 2015-02-12 |
|           2 |      9 | 2015-04-12 |
+-------------+--------+------------+

You can do this using this query:

SELECT
  customer_id,
  sum(if(month(date) = 1, amount, 0))  AS Jan,
  sum(if(month(date) = 2, amount, 0))  AS Feb,
  sum(if(month(date) = 3, amount, 0))  AS Mar,
  sum(if(month(date) = 4, amount, 0))  AS Apr,
  sum(if(month(date) = 5, amount, 0))  AS May,
  sum(if(month(date) = 6, amount, 0))  AS Jun,
  sum(if(month(date) = 7, amount, 0))  AS Jul,
  sum(if(month(date) = 8, amount, 0))  AS Aug,
  sum(if(month(date) = 9, amount, 0))  AS Sep,
  sum(if(month(date) = 10, amount, 0)) AS Oct,
  sum(if(month(date) = 11, amount, 0)) AS Nov,
  sum(if(month(date) = 12, amount, 0)) AS `Dec`
FROM sales
GROUP BY customer_id;

And the output:

+-------------+------+------+------+------+------+------+------+------+------+------+------+------+
| customer_id | Jan  | Feb  | Mar  | Apr  | May  | Jun  | Jul  | Aug  | Sep  | Oct  | Nov  | Dec  |
+-------------+------+------+------+------+------+------+------+------+------+------+------+------+
|           1 |   13 |  663 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
|           2 |   22 |   32 |    0 |    9 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
+-------------+------+------+------+------+------+------+------+------+------+------+------+------+

Great answer! But how could I have the output display just ONE ROW for each distinct customer_id? — helloworld, Feb 26 '15 at 16:21
Get rid of the grouping on `month(date)`. The sum+case I wrote about in comments above is the standard SQL way to do this but otherwise I think they're equivalent. — shawnt00, Feb 26 '15 at 16:57

Ataboy Josef · Answer 2 · 2015-02-26T04:50:36.037

1

Try this:

SELECT `Customer_id` AS Customer,
SUM(`amount`) AS MONTHNAME(`date`)
FROM `sales`
GROUP BY YEAR(`date`), MONTH(`date`);

edited Feb 26 '15 at 04:50

answered Feb 26 '15 at 04:18

Ataboy Josef

2,087
3
22
27

Thanks for the answer. I don't have distinct sales value columns for each month. I`m trying to create them from the date field. – helloworld Feb 26 '15 at 04:36
Provide the table structure. – Ataboy Josef Feb 26 '15 at 04:39
Three columns: | Customer_Id | amount | date | – helloworld Feb 26 '15 at 04:41

score 1 · Answer 3 · answered Aug 19 '15 at 09:37

1

MySQL provides the WITH ROLLUP modifier for the GROUP BY clause. Take a look at this.

... GROUP BY customer_id WITH ROLLUP

Hope it helps.

answered Aug 19 '15 at 09:37

bayu

401
3
4

score 0 · Answer 4 · answered Feb 26 '15 at 04:40

0

Try this code.

SELECT Customer_id, SUM(amount), MONTHNAME(date) FROM sales_table GROUP BY MONTH(date);

answered Feb 26 '15 at 04:40

Inshaf Mahath

383
3
3

Thanks! But this query does not provide the sum for each Customer_id, but it does group the total amount for the month. – helloworld Feb 26 '15 at 04:47
Try adding GROUP BY MONTH(date), Customer_id – Inshaf Mahath Feb 26 '15 at 05:00
Thanks but not exactly what I´m looking for – helloworld Feb 26 '15 at 05:04

score 0 · Answer 5 · answered Feb 26 '15 at 17:01

This query gets the desired result:

SELECT Customer_id,

Sum(CASE WHEN MONTH(date) = 1 THEN amount END) AS Jan,
Sum(CASE WHEN MONTH(date) = 2 THEN amount END) AS Feb,
Sum(CASE WHEN MONTH(date) = 3 THEN amount END) AS Mar,
...

FROM table_name

GROUP BY Customer_id

Thanks to @shawnt00 for the heads up ;)

score 0 · Answer 6 · answered Jul 22 '20 at 19:43

0

If you change each line like this in the answer from Piotr

sum(if(month(date) = 1, amount, 0))  AS Jan,

to

sum(if((month(date) = 1 AND year(date) = 2017 ),  amount,0))  AS Jan_17,

you can split the table when there is more than a year of data

answered Jul 22 '20 at 19:43

user2818170

107
1
10

If you want to add a small change to an already accepted answer, try editing the answer, instead of adding a new answer. People usually only look at the first answer, maybe second, so it's very unlikely that they will notice your suggestion. When editing an answer, it's probably best is if you add your suggestion at the very end, and indicate it's an addition – Marjeta Jul 22 '20 at 22:12

Mysql sales table grouped by customer and displayed by month columns pivot query

6 Answers6