CODEWITHSUNDEEP
mysql
Armin Hierstetter
Armin Hierstetter

Reputation: 1098

Select multiple years in separate columns

I have a table that stores the date and price of purchases.

For example

date       | price
---------------------
2014-1-12  | 6.50
2014-2-34  | 10.99
2015-1-01  | 3.22
2015-2-14  | 4.12

And so on.

What I want to achieve: A query that outputs the SUM of the purchases grouped per month of the year.

The IMPORTANT thing is, though, that I need to have the different years in COLUMNS to be able to make a graph with a separate line for each year. So the output I need is this:

MONTH | 2014    |  2015 
JAN   |  123.23 |  99.1
FEB   |  457.00 |  122.00
MAR   |  299.99 |  789.12
...   |
NOV   |  333.33 | 10.99
DEC   |  100.00 | 20.10

Is this even possible? I searched quite a long time for things like "year on year" query etc. But I could not find anything.

Any help is greatly appreciated!

Upvotes: 2

Views: 2275

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Just use conditional aggregation:

select monthname(date) as mon,
       sum(case when year(date) = 2014 then price else 0 end) as price_2014,
       sum(case when year(date) = 2015 then price else 0 end) as price_2015
from table t
group by monthname(date)
order by max(month(date));

Upvotes: 6

Related Questions