patternsqlMinor
SUM Case Logic with Distinct
Viewed 0 times
casedistinctwithlogicsum
Problem
This is my first post, so please be gentle :)
I have a couple of queries:
Now I've been asked to pull the data for the last year, so I thought I would use a case statement, but cannot figure out how to incorporate the distinct user part into the sum case statement....so I get huge numbers, obviously
What is returning is the following, which is a sum. I am looking for a distinct count for each way.
Thanks for any assistance
I have a couple of queries:
SELECT COUNT(DISTINCT(h.USERID))
FROM MHGROUP.USERHISTORY h with (NOLOCK)
JOIN MHGROUP.DOCUSERS u with (NOLOCK) on h.USERID = u.USERID
where h.CUSTOM2 = 'Work Web'
and h.ACTIONDATETIME >= GETUTCDATE()-30
and u.LOGIN = 'y'SELECT COUNT(DISTINCT(h.USERID))
FROM MHGROUP.USERHISTORY h with (NOLOCK)
JOIN MHGROUP.DOCUSERS u with (NOLOCK) on h.USERID = u.USERID
where h.CUSTOM2 = 'OUTLOOK'
and h.ACTIONDATETIME >= GETUTCDATE()-30
and u.LOGIN = 'y'
Now I've been asked to pull the data for the last year, so I thought I would use a case statement, but cannot figure out how to incorporate the distinct user part into the sum case statement....so I get huge numbers, obviously
Select 'Date' = format(h.ACTIONDATETIME, 'MMM yyyy')
, sum(CASE WHEN h.CUSTOM2 = 'Work Web' THEN 1 ELSE 0 END) WorkWeb
, sum(CASE WHEN h.CUSTOM2 = 'OUTLOOK' THEN 1 ELSE 0 END) Outlook
, 'UserCount' = COUNT(DISTINCT(h.USERID))
From MHGROUP.USERHISTORY h with (NOLOCK)
JOIN MHGROUP.DOCUSERS u with (NOLOCK) on h.USERID = u.USERID
where u.LOGIN = 'y'
and Year(h.ACTIONDATETIME)>2017
Group By Format(h.ACTIONDATETIME,'MMM yyyy')
What is returning is the following, which is a sum. I am looking for a distinct count for each way.
Thanks for any assistance
Solution
count(column) does not count null values so you could use count() with a case statement that returns h.USERID or null., COUNT(DISTINCT CASE WHEN h.CUSTOM2 = 'Work Web' THEN h.USERID ELSE NULL END) UserCountWorkWeb
, COUNT(DISTINCT CASE WHEN h.CUSTOM2 = 'OUTLOOK' THEN h.USERID ELSE NULL END) UserCountOutlookCode Snippets
, COUNT(DISTINCT CASE WHEN h.CUSTOM2 = 'Work Web' THEN h.USERID ELSE NULL END) UserCountWorkWeb
, COUNT(DISTINCT CASE WHEN h.CUSTOM2 = 'OUTLOOK' THEN h.USERID ELSE NULL END) UserCountOutlookContext
StackExchange Database Administrators Q#246955, answer score: 6
Revisions (0)
No revisions yet.