SQLTeam.com | Weblogs | Forums

Count Distinct with Criteria

#1

Hi everyone, I hoping someone could help me. I would like to count distinct telephone numbers but only if they don't start +443, for these I just want to count the total and then add the 2 figures together.

+447411912345
+447411912345
+441908543211
+443456002312
+447974343211
+447845312345
+443456002312

So for the data above, I would like the answer to be 6, 4 distinct non +443 numbers, and 2 +443 numbers.

Kind regards

#2 

SELECT 
    COUNT(DISTINCT CASE WHEN telephone NOT LIKE '443%' THEN telephone END) + 
    COUNT(CASE WHEN telephone LIKE '443%' THEN 1 ELSE 0 END) AS final_count
FROM dbo.table_name
#3

Hi Scott, thanks as always. When I run the code I get a total of 13 but there are only 7 records in my query.

COUNT(DISTINCT CASE WHEN telephone NOT LIKE '443%' THEN telephone END) = 6
COUNT(CASE WHEN telephone LIKE '443%' THEN 1 ELSE 0 END) = 7

#4

OOPS, my bad:


SELECT 
    COUNT(DISTINCT CASE WHEN telephone NOT LIKE '443%' THEN telephone END) + 
    SUM(CASE WHEN telephone LIKE '443%' THEN 1 ELSE 0 END) AS final_count
FROM dbo.table_name
1 Like
#5

Perfect thanks alot.