CODEWITHSUNDEEP
mysqldate
Samuurai
Samuurai

Reputation: 395

SQL Query with days of month as columns

I'm trying to create a query which will show sales like this:

     Jan  Feb  Mar  ...
2011 1253 1381 1973 ...
2012 3124 3829 1823 ...

Along the top shows months from Jan to Dec and the left hand column shows the year. The data is SUM'd figures for the entire period of that year/month.

The table holds sales records with totals and datetime field.

I've seen some options for this using UNPIVOT, but I can't get my head around it.

Thanks

Upvotes: 0

Views: 1604

Answers (1)

DRapp
DRapp

Reputation: 48139

Without seeing your table structure, you could do something like

select
      year( YT.YourDateColumn ) as SalesYear,
      sum( if( month( yt.YourDateColumn ) = 1, YT.SalesAmt, 000000.00 )) as Jan,
      sum( if( month( yt.YourDateColumn ) = 2, YT.SalesAmt, 000000.00 )) as Feb,
      sum( if( month( yt.YourDateColumn ) = 3, YT.SalesAmt, 000000.00 )) as Mar,
      sum( if( month( yt.YourDateColumn ) = 4, YT.SalesAmt, 000000.00 )) as Apr,
      sum( if( month( yt.YourDateColumn ) = 5, YT.SalesAmt, 000000.00 )) as May,
      sum( if( month( yt.YourDateColumn ) = 6, YT.SalesAmt, 000000.00 )) as Jun,
      sum( if( month( yt.YourDateColumn ) = 7, YT.SalesAmt, 000000.00 )) as Jul,
      sum( if( month( yt.YourDateColumn ) = 8, YT.SalesAmt, 000000.00 )) as Aug,
      sum( if( month( yt.YourDateColumn ) = 9, YT.SalesAmt, 000000.00 )) as Sep,
      sum( if( month( yt.YourDateColumn ) = 10, YT.SalesAmt, 000000.00 )) as Oct,
      sum( if( month( yt.YourDateColumn ) = 11, YT.SalesAmt, 000000.00 )) as Nov,
      sum( if( month( yt.YourDateColumn ) = 12, YT.SalesAmt, 000000.00 )) as Dec
   from
      YourTable YT
   group by
      year( YT.YourDateColumn )
   order by
      Year( YT.YourDateColumn )

Upvotes: 1

Related Questions