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?