mysql - sql如何创建一列月份并显示它们的总和

Question

我有两列数量 + 日期，如何创建一个带有月份名称的列和另一个显示每个月总和(数量)的列？

Date     |Qty
----     |---
2014-1-2 | 5
2014-1-3 | 9
2014-1-4 | 100
2014-1-5 | 200
.
.
.




result :

Month    |Qty
----     |---
JAN      | 500
Feb      | 900
Mar      | 200
.
.

我得到了数量但无法得到月份列怎么办？

我正在通过这个查询来做这件事

SELECT SUM(case when Date BETWEEN '2014-1-1' and '2014-2-1' then Qty else 0 end) as Qty from table

UNION

SELECT SUM(case when Date BETWEEN '2014-2-1' and '2014-3-1' then Qty else 0 end) as Qty from table

UNION

SELECT SUM(case when Date BETWEEN '2014-3-1' and '2014-4-1' then Qty else 0 end) as Qty from table

UNION

SELECT SUM(case when Date BETWEEN '2014-4-1' and '2014-5-1' then Qty else 0 end) as Qty from table

UNION

SELECT SUM(case when Date BETWEEN '2014-5-1' and '2014-6-1' then Qty else 0 end) as Qty from table
UNION

SELECT SUM(case when Date BETWEEN '2014-6-1' and '2014-7-1' then Qty else 0 end) as Qty from table

UNION

SELECT SUM(case when Date BETWEEN '2014-7-1' and '2014-8-1' then Qty else 0 end) as Qty from table

UNION

.
.

Answer 1

试试GROUP BY DATE_FORMAT

SELECT SUM(Qty) as Qty,DATE_FORMAT(Date, '%b') Month from table
GROUP BY DATE_FORMAT(Date,'%Y-%m')