Update to SQL - Count Values of Zero

I have a query that currently counts distinct Key3:

SELECT username, type, count(distinct r.key2) as AdCount, count(distinct r.key3) as SubCount,   CASE WHEN DATEPART(hour, ra.action_time) >= 12 THEN  CONVERT(DATETIME, CONVERT(VARCHAR, ra.action_time, 102), 102)
                ELSE   CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(Day, -1,ra.action_time), 102), 102)  
             END   AS ShiftDate, SUBSTRING(CONVERT(char(12),ra.action_time,108),1,2) as Hour, count(*) as RecordCount

This works when the key is used.  But there are records where the Key3 is 0 (zero).  There are cases where there are multiple Key3s and I want it to add these up.  Can you show me how to change the code above to accumulate the Key3 values when they = 0?  

Scotto13
Scotto13Asked:
Who is Participating?
 
jrb1Connect With a Mentor Commented:
SELECT username, type, count(distinct r.key2) as AdCount, count(distinct r.key3) as SubCount,  
sum(case when r.key3=0 then 1 else 0 end) as Key3ZeroCount,  
CASE WHEN DATEPART(hour, ra.action_time) >= 12 THEN  CONVERT(DATETIME, CONVERT(VARCHAR, ra.action_time, 102), 102)
                ELSE   CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(Day, -1,ra.action_time), 102), 102)  
             END   AS ShiftDate, SUBSTRING(CONVERT(char(12),ra.action_time,108),1,2) as Hour, count(*) as RecordCount

You can use a CASE and SUM to add these up.
0
 
Einstine98Commented:
count will count them all (0) and Multiples...

so

Key3
0
0
0
4
5
6
6
6

Count(key3) = 8
0
 
DireOrbAntCommented:
Unsure here, simply remove distinct?

SELECT username, type, count(distinct r.key2) as AdCount, count(r.key3) as SubCount,   CASE WHEN DATEPART(hour, ra.action_time) >= 12 THEN  CONVERT(DATETIME, CONVERT(VARCHAR, ra.action_time, 102), 102)
                ELSE   CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(Day, -1,ra.action_time), 102), 102)  
             END   AS ShiftDate, SUBSTRING(CONVERT(char(12),ra.action_time,108),1,2) as Hour, count(*) as RecordCount

So you want to count the rows? In this case count(*) as SubCount would be better.

Can you provide the rest of the SQL statement and an expected result?
0
 
Scotto13Author Commented:
jrb1,

you got exactly what I needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.