Multiple Cases

Question

Multiple Cases

sharonsql2013

SSCertifiable

Points: 5908
More actions
December 18, 2013 at 1:17 pm

#280460

I need to count all the records that meet the conditions in the case statement.
Following is the select after the CTE1 to display the count if each of the condition is met.
Can anyone tell me whats wrong with it??
Select
Count(Case when CTE1.Status = 'No' Then 1 Else Null
when CTE1.Agent_Status]= 'Aged' Then 1 Else Null
when CTE1.Platform] = Production Then 1 Else Null
When CTE1.Critical = 'Yes' Then 1 Else Null
End as CriticalData)
From CTE1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

sharonsql2013 (12/18/2013)
I need to count all the records that meet the conditions in the case statement.
Following is the select after the CTE1 to display the count if each of the condition is met.
Can anyone tell me whats wrong with it??
Select
Count(Case when CTE1.Status = 'No' Then 1 Else Null
when CTE1.Agent_Status]= 'Aged' Then 1 Else Null
when CTE1.Platform] = Production Then 1 Else Null
When CTE1.Critical = 'Yes' Then 1 Else Null
End as CriticalData)
From CTE1

Will also get rid of any "Null ignored by aggregate" warnings but since I do not have your data, recognize that I've not tested it.

Select

SUM(Case when CTE1.Status = 'No' Then 1

when CTE1.Agent_Status]= 'Aged' Then 1

when CTE1.Platform] = Production Then 1

When CTE1.Critical = 'Yes' Then 1

ELSE 0

End as CriticalData)

From CTE1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 2

I get this error : 'SUM' is not a recognized built-in function name.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

sharonsql2013 (12/18/2013)
I get this error : 'SUM' is not a recognized built-in function name.

You'll have to show all the code you ran. And verify this is SQL Server.

SUM() is definitely a valid construct in SQL Server.

CKX SSCrazy Points: 2519 More actions · Answer 4

I think it's the column alias causing the error.

Try it like this:

Select

SUM(Case when CTE1.Status = 'No' Then 1

when CTE1.Agent_Status= 'Aged' Then 1

when CTE1.Platform = Production Then 1

When CTE1.Critical = 'Yes' Then 1

ELSE 0

End)

From CTE1

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 5

CK2 (12/19/2013)
I think it's the column alias causing the error.
Try it like this:
Select
SUM(Case when CTE1.Status = 'No' Then 1
when CTE1.Agent_Status= 'Aged' Then 1
when CTE1.Platform = Production Then 1
When CTE1.Critical = 'Yes' Then 1
ELSE 0
End)
From CTE1

^^^ That code will get you the count of rows where ANY of those conditions is true. To get the count of rows where ALL of those conditions are true, you need this:

SELECT SUM(CASE WHEN CTE1.Status = 'No' AND CTE1.Agent_Status= 'Aged' AND CTE1.Platform = 'Production' AND CTE1.Critical = 'Yes' THEN 1

ELSE 0

END)

FROM CTE1

Jason Wolfkill