在SQL中转移数据(计算每月销售数据)

Question

我的数据总共有100行

Day         Item    Price Unit
01/01/17    jeans     15   4
01/02/17    shirt     10   3
01/01/17    skirt     20   7
01/04/17    skirt     25   1
02/01/17    jeans     5   4
02/02/17    shirt    15   3
02/01/17    skirt     24   7
03/04/17    skirt     25   5

我想将我的数据表示为1月和2月的每月销售

数据应表示为

month jeans skirt shirt total
Jan
Feb

我在代码中遇到错误

SELECT MONTH,
MAX(CASE WHEN ITEM = 'JEANS' THEN GRANDTOTAL END) GRANDTOTAL,
MAX(CASE WHEN ITEM = 'SHIRT' THEN GRANDTOTAL END) AS GRANDTOTAL,
MAX(CASE WHEN ITEM = 'SKIRT' THEN GRANDTOTAL END) AS GRANDTOTAL, 
FROM SALES 
GROUP BY MONTH ;

我尝试了所有方法，但是出现错误，请帮助：(

Answer 1

每个项目的销售定义为价格x单位的总和，而总销售额是所有项目的销售总和。

请参阅演示：http://sqlfiddle.com/#！9/2a9e8f/1

SELECT date_format(day, '%b') as `month`,
sum(case when item='jeans' then price*unit else 0 end) as jeans,
sum(case when item='shirt' then price*unit else 0 end) as shirts,
sum(case when item='skirt' then price*unit else 0 end) as skirts,
sum(price*unit) as total
FROM myTbl
where month(day) <= 2
GROUP BY date_format(day, '%b')
ORDER BY month(day);
result:
month   jeans   shirts  skirts  total
Jan       60      30         165         255
Feb   20        45       168         233