Group by column

[Jonob]

Hi all,

 

I have the following tables:

 

account

account_id (PK)

name

 

transaction

tranaction_id (PK)

account_id (FK)

date_post

amount

 

And I have the following query which does a simple group by clause:

 

SELECT a.account_id, a.name, sum (t.amount) as sum_total

FROM account a

LEFT JOIN transaction t on a.account_id = t.account_id

GROUP BY a.account_id

 

And this gives me a nice and simple sum by account_id. All good.

 

Now, assume that I want to break this data down a bit further, and show the sum for each account_id and by month+year. So, we would have account_id for each row, and month+year for each column heading. So, it would look something like:

 

account_id

Jan 2009

Feb 2009

Mar 2009

 

1

10

0

30

 

2

20

25

35

 

3

0

0

5

 

I should also add that I will know beforehand what the month ranges are, so just a static example would be fine for now.

 

Any pointers on the easiest way to do this? Many thanks for your help.

[luca200]

Not tested, just give it a try

 

 

SELECT a.account_id, a.name,

ifnull(sum (if(year(date_post)=2009 and month(Date_post)=1,t.amount,0),0) as `January 2009`,

ifnull(sum (if(year(date_post)=2009 and month(Date_post)=2,t.amount,0),0) as `February 2009`,

ifnull(sum (if(year(date_post)=2009 and month(Date_post)=3,t.amount,0),0) as `March 2009`,

FROM account a

LEFT JOIN transaction t on a.account_id = t.account_id

GROUP BY a.account_id