Select Case & SUM Question 1

[jjohns09]

I have the following query that I can't get to work correctly. What I want is that when REASON = Idle 1 or Idle 2 to rename the row just Idle and leave all other REASON codes alone, and to display the sum of the durations for each REASON.

SELECT CASE WHEN REASON = 'Idle 1' OR REASON = 'Idle 2'
THEN 'Idle' ELSE REASON AS REASON,
SUM(DURATION) AS SUM_DURATION FROM tblAgentEvents
GROUP BY REASON

What I am getting is 2 rows for 'Idle'. All other rows are displaying correctly.

REASON SUM_DURATION
Idle 1003
Idle 3450
Break 2347
Lunch 10350
Meeting 5670

Can someone please help me with this?

Thank you,

Jeff

 

[fluteplr]

use the following

SELECT CASE WHEN REASON = 'Idle 1' OR REASON = 'Idle 2'
THEN 'Idle' ELSE REASON AS REASON,
SUM(DURATION) AS SUM_DURATION
FROM tblAgentEvents
group by
CASE WHEN REASON = 'Idle 1' OR REASON = 'Idle 2'
THEN 'Idle' ELSE REASON

you have to use the same case statement in the group. What is happening is that the grouping is done before the case in the select is evaluated so the two groups are actually your original idle1 and 2 .

 

[jjohns09]

Thank you for the assistance. It works perfectly now.

God Bless America