CODEWITHSUNDEEP
mysqlsql
Mariachi
Mariachi

Reputation: 305

Sales per agent, year and month

I have a table with agentID, transDate and transValue. How do I report all transValue per agent, year and month? The columns are supposed to be as follows: agentID|year|January|February... Under every month must be transValue summed up. Now I have a part of code:

select agentID, year(transDate), sum(transValue) as January
from operations
where month(transDate) = 01
group by agentID, year(transDate)

I dont know how to add the following months. Any suggestions?

Upvotes: 0

Views: 22

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

You can use conditional aggregation to generate that report -

select agentID, year(transDate),
       sum(CASE WHEN month(transDate) = 1 THEN transValue END) as January,
       sum(CASE WHEN month(transDate) = 2 THEN transValue END) as february,
       sum(CASE WHEN month(transDate) = 3 THEN transValue END) as March,
       sum(CASE WHEN month(transDate) = 4 THEN transValue END) as April,
       sum(CASE WHEN month(transDate) = 5 THEN transValue END) as May,
       sum(CASE WHEN month(transDate) = 6 THEN transValue END) as June,
       sum(CASE WHEN month(transDate) = 7 THEN transValue END) as July,
       sum(CASE WHEN month(transDate) = 8 THEN transValue END) as August,
       sum(CASE WHEN month(transDate) = 9 THEN transValue END) as Spetember,
       sum(CASE WHEN month(transDate) = 10 THEN transValue END) as October,
       sum(CASE WHEN month(transDate) = 11 THEN transValue END) as November,
       sum(CASE WHEN month(transDate) = 12 THEN transValue END) as December
  from operations
 group by agentID, year(transDate)

Upvotes: 2

Related Questions