3

This is my first post, so please be gentle :)

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.

too many users

Thanks for any assistance

1
  • 2
    It would be very helpful if you could update your question to include a Minimal, Reproducible Example. This would include table definitions (DDL) and sample data in the form of actual insert statements along with expected results. Sep 4 '19 at 13:43
6

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) UserCountOutlook
2
  • ELSE NULL is not necessary (but it makes the example more illustrative).
    – Lennart
    Sep 4 '19 at 14:38
  • Awesome answer Mikael. Thanks so very much! You made my month!
    – iManageGuy
    Sep 4 '19 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.