2

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!

Armin Hierstetter
  • 1,078
  • 2
  • 12
  • 27
  • See if this is similar: http://stackoverflow.com/questions/7674786/mysql-pivot-table – PaulF Jul 15 '15 at 12:15
  • I might not have time to put a solution together, but I wanted to make a few comments. First, your query would be "breakable" in a plain select statement because you can't (without using dialect specific pivots) use a simple function to dynamically add columns as the years go by. Are you okay manually changing this each year? As an alternative, it would be relatively trivial to write a query that groups by year and month (with a case statement to change it to text) and then format this on the application side. Are you open to some application-side code? Which language? – Palu Macil Jul 15 '15 at 12:16
  • It will be simple if you expect to have columns for "current year", "year - 1", "year - 2"... i.e. relative to current date. – i486 Jul 15 '15 at 12:18
  • Palu: I would be open to server side code, but you already directed me in the right direction. – Armin Hierstetter Jul 15 '15 at 12:41
  • To my way of thinking, problems of data display should always be resolved in presentation layer code. – Strawberry Jul 15 '15 at 12:57

1 Answers1

6

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));
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786