CODEWITHSUNDEEP
mysql
Warren Charles
Warren Charles

Reputation: 3

aggregating values by month, Group by statement in mysql

I have a question on how to group the following statement into months.
Database is MYSQL

Desired result is:

DRNAME, Jan, Feb, Mar, April, May ,June
SISHEN, 0, 0, 100, 250, 450, 500, 0

Result I get is:

DRNAME, Jan, Feb, Mar, April, May ,June
SISHEN, 0, 0, 100, 0, 0, 0,0
SISHEN, 0, 0,0,250,0,0,0
SISHEN, 0, 0, 0,0 , 450, 0, 0

query is

select  drname,
case when month(loaddate) = 1 then sum(drvalue) end as 'Jan',<br>
case when month(loaddate) = 2 then sum(drvalue) end as 'Feb',<br>
case when month(loaddate) = 3 then sum(drvalue) end as 'March',<br>
case when  month(loaddate) = 4 then sum(drvalue) end as 'April',<br>
case when  month(loaddate) = 5 then sum(drvalue) end as 'May',<br>
case when  month(loaddate) = 6 then sum(drvalue) end as 'June'<br>
from tblloadschedule<br>
where cancelloadflag = 'N' and drname like 'sish%'<br>
group by drname,month(loaddate)

Upvotes: 0

Views: 33

Answers (4)

Warren Charles
Warren Charles

Reputation: 3

The method from A. Colonna with the IF definitely worked out more efficient on the server and returned the desired results.

Many thanks to all who commented.
The code I used is: select drname,
sum((month(loaddate) = 1 ) * drvalue) as 'Jan',
sum((month(loaddate) = 2 ) * drvalue) as 'Feb',
sum((month(loaddate) = 3 ) * drvalue) as 'March',
sum((month(loaddate) = 4 ) * drvalue) as 'April',
sum((month(loaddate) = 5 ) * drvalue) as 'May',
sum((month(loaddate) = 6 ) * drvalue) as 'June'
from tblloadschedule
where cancelloadflag = 'N'
group by drname

Upvotes: 0

A. Colonna
A. Colonna

Reputation: 872

You don't need case which is expensive:

select drname, 
   sum(if(month(loaddate) = 1 ,drvalue,0) as 'Jan',
   sum(if(month(loaddate) = 2 , drvalue ,0) as 'Feb',
   sum(if(month(loaddate) = 3 , drvalue ,0) as 'March',
   sum(if(month(loaddate) = 4 , drvalue ,0) as 'April',
   sum(if(month(loaddate) = 5 , drvalue ,0) as 'May',
   sum(if(month(loaddate) = 6 , drvalue ,0) as 'June'
from tblloadschedule
where cancelloadflag = 'N' and drname like 'sish%'
group by drname

EDIT without use of IF:

select drname, 
   sum((month(loaddate) = 1 ) * drvalue) as 'Jan',
   sum((month(loaddate) = 2 ) * drvalue) as 'Feb',
   sum((month(loaddate) = 3 ) * drvalue) as 'March',
   sum((month(loaddate) = 4 ) * drvalue) as 'April',
   sum((month(loaddate) = 5 ) * drvalue) as 'May',
   sum((month(loaddate) = 6 ) * drvalue) as 'June'
from tblloadschedule
where cancelloadflag = 'N' and drname like 'sish%'
group by drname

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Modify your query to take aggregates of the conditional CASE expressions:

SELECT
    drname,
    SUM(CASE WHEN MONTH(loaddate) = 1 THEN drvalue END) AS Jan,
    SUM(CASE WHEN MONTH(loaddate) = 2 THEN drvalue END) AS Feb,
    SUM(CASE WHEN MONTH(loaddate) = 3 THEN drvalue END) AS March,
    SUM(CASE WHEN MONTH(loaddate) = 4 THEN drvalue END) AS April,
    sum(CASE WHEN MONTH(loaddate) = 5 THEN drvalue END) AS May,
    sum(CASE WHEN MONTH(loaddate) = 6 THEN drvalue END) AS June
FROM tblloadschedule
WHERE
    cancelloadflag = 'N' AND drname LIKE 'sish%'
GROUP BY
    drname;

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

You need to remove month(loaddate) from the group by clause and then use conditional aggregation instead:

select drname, 
   sum(case when month(loaddate) = 1 then drvalue else 0 end) as 'Jan',
   sum(case when month(loaddate) = 2 then drvalue else 0 end) as 'Feb',
   sum(case when month(loaddate) = 3 then drvalue else 0 end) as 'March',
   sum(case when month(loaddate) = 4 then drvalue else 0 end) as 'April',
   sum(case when month(loaddate) = 5 then drvalue else 0 end) as 'May',
   sum(case when month(loaddate) = 6 then drvalue else 0 end) as 'June'
from tblloadschedule
where cancelloadflag = 'N' and drname like 'sish%'
group by drname

Upvotes: 1

Related Questions