Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Distinct Values in a Case Statement

Author  Topic 

mattie
Starting Member

13 Posts

Posted - 2012-10-15 : 16:23:58
How do I keep from summing duplicate values in KEY1 (VARCHAR) field in case statement? Distinct of course does not work, because it yields 1 for all the rows. Thx


Sum (Case When KEY1 Not Like All ('DOA%' , 'APP%' , 'IMC%') Then 1 Else 0 End) as Load ,

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-15 : 16:32:38
Try COUNT DISTINCT:
SELECT
COUNT
(DISTINCT
Case
When KEY1 Not Like ('DOA%') AND KEY1 Not Like ('APP%') AND KEY1 Not Like ('IMC%') Then KEY1
Else NULL
End
) as Load
EDIT: wasn't paying attention to the LIKE.. was thinking NOT IN.
Go to Top of Page

mattie
Starting Member

13 Posts

Posted - 2012-10-15 : 16:44:05
Perfect! Thx.
Go to Top of Page
   

- Advertisement -