Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Group By with Case


Group By with Case

Author
Message
rothj
rothj
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1435 Visits: 3603
I'm trying to query for Group By with a Case.
I want a total per Date but there are some Departments that have a different rule for calculating the total.
I believe I need to move the Dept into the Case statement but I'm having difficulty with the syntax.
Here is what I currently have:
DECLARE @FYStartDate  AS DATE
SET @FYStartDate = '2012-07-01'

SELECT PPDate, Dept,

CASE
When Dept in ('8310', '8330', '8380', '8500', '9360', '9550', '9610') THEN
Sum([Value]/520) --measured quarterly

WHEN Dept = '7120' THEN
Sum([value]) --hard coded

ELSE
Sum(Value/80) --measured bi-weekly
END AS 'Earned FTE'

FROM #trends

WHERE Dept not in ('6570','6580','6645','7221','9831') and --don't include these dept's
PPDate >= @FYStartDate

Group By PPDate, Dept
Order By PPDate, Dept


When I try removing the Dept from the SELECT/Group By I get the error:
Column DEPT is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks for any insight.
Here's the table/data (attached)

This is my goal:


07/14/2012 5.69
07/28/2012 6.12
08/11/2012 5.80
08/25/2012 5.21
09/08/2012 5.90
09/22/2012 5.89
10/06/2012 6.08
10/20/2012 6.26

Attachments
test_table_data.txt (13 views, 1.00 KB)
Chris Harshman
Chris Harshman
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2689 Visits: 3289
if you don't want to group by dept, then the SUM needs to be outside the CASE structure, like this:
DECLARE @FYStartDate  AS DATE
SET @FYStartDate = '2012-07-01'
SELECT PPDate, --Dept,
SUM(CASE WHEN Dept in ('8310', '8330', '8380', '8500', '9360', '9550', '9610') THEN [Value]/520 --measured quarterly
WHEN Dept = '7120' THEN [value] --hard coded
ELSE Value/80 --measured bi-weekly
END) AS 'Earned FTE'
FROM #trends
WHERE Dept not in ('6570','6580','6645','7221','9831') --don't include these dept's
AND PPDate >= @FYStartDate
GROUP BY PPDate--, Dept
ORDER BY PPDate--, Dept


rothj
rothj
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1435 Visits: 3603
Wow, that was easy!
Thanks, it gives me what I'm looking for! Appreciated!
Chris Harshman
Chris Harshman
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2689 Visits: 3289
no problem, you made it easy for people to help you by posting the script with the CREATE, and INSERT
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search