Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sum of Counts Expand / Collapse
Author
Message
Posted Tuesday, December 24, 2013 8:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 229, Visits: 694
I am trying to get a sum of all the counts if both the conditions are met in the case case statement.
Here is the CTE.. I am expecting a single digit output. However , when I try to Sum the counts I get an error stating that its not allowed. Please advise

With CTE1 As
(SELECT [Device_Name]
, Case when [Platform] Like 'Production%' Then 'Production'
When [Platform] Like 'Development%' Then 'Dev-Non Production'
When [Platform] Like 'Test' Then 'Non Production'
End as [Platform_Display]
,[Group_Owner]
, Case when [Exclusion_Status] IS Null Then 'No'
When [Exclusion_Status]= 'No' Then 'No'
When [Exclusion_Status]= 'Yes' Then 'Yes'
End as [Exclusion_Status_Now]

FROM ProductionData
)

Select Case when CTE1.[Exclusion_Status_now] = 'No'
and CTE1.[Platform_Display] = 'Production'
Then (COUNT(CTE1.[Exclusion_Status_now]) + COUNT([Platform_Display])
Else Null
End
from CTE1 where CTE1.[Group_Owner] LIKE 'Prod'
group by [Exclusion_Status_now],[Platform_Display]

Post #1525766
Posted Tuesday, December 24, 2013 9:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 229, Visits: 694
No worried .. I figured it out:)
Its
Sum (case .... then 1 else 0)
Post #1525773
Posted Tuesday, December 24, 2013 9:27 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:26 PM
Points: 3,420, Visits: 5,350
sharonsql2013 (12/24/2013)
No worried .. I figured it out:)
Its
Sum (case .... then 1 else 0)


I think you meant:

Sum (case .... then 1 else 0 end)


If you're counting, it might be more intuitive this way:

COUNT(case .... then 1 end)


Merry Christmas!



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1525852
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse