CODEWITHSUNDEEP
mysqlsql
phpnet
phpnet

Reputation: 923

sql How to create a column of months and display sum of them

I have two columns of quantity + dates , How to create a column with months name and other column which display the sum(quantity) of every month ?

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
.
.

I have got the quantity but not able to get the months column how to do it ?

I am doing this by this query

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

.
.

Upvotes: 2

Views: 449

Answers (1)

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Try GROUP BY DATE_FORMAT

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

Upvotes: 3

Related Questions