Sql-server – SUM Case Logic with Distinct

sql servert-sql

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.

Thanks for any assistance

Best Answer

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

Related Solutions

Sql-server – Create Calculated Fields from Distinct Values in Second Table

This looks lika a PIVOT operation, where you want an aggregate (the count) of your error codes, grouped by date (on rows) and error code (on columns).

First off, pivot reports are probably easier to design in your reporting tool, Report Builder, so I would try this query first and try to format the results into a pivot table in the GUI. I don't know Report Builder very well, but the query should look something like this:

SELECT cal.cal_date,
       ec.ERRCODE,
       COUNT(*) AS [count]
FROM dbo.CreateCalendar(@p_year) AS cal
LEFT JOIN Errors.ERRCODE AS ec ON
    cal.cal_date=ec.ERRDATE AND
    ec.ERRTYPE='BATCH'
GROUP BY cal.cal_date, ec.ERRCODE;

If that doesn't work, you can make SQL Server do the pivoting for you, but the downside is that you have to explicitly specify each column (error code) manually in the query, which is repetitive and any future error code won't show up without editing the query later on.

SELECT cal.cal_date,
       SUM((CASE WHEN ec.ERRCODE='0001' THEN 1 ELSE 0 END)) AS [0001],
       SUM((CASE WHEN ec.ERRCODE='0002' THEN 1 ELSE 0 END)) AS [0002],
       SUM((CASE WHEN ec.ERRCODE='0003' THEN 1 ELSE 0 END)) AS [0003],
       SUM((CASE WHEN ec.ERRCODE='0004' THEN 1 ELSE 0 END)) AS [0004]
FROM dbo.CreateCalendar(@p_year) AS cal
LEFT JOIN Errors.ERRCODE AS ec ON
    cal.cal_date=ec.ERRDATE AND
    ec.ERRTYPE='BATCH'
GROUP BY cal.cal_date
ORDER BY cal.cal_date;

For more reading on this type of operation, take a look at GROUP BY, aggregate functions (like COUNT() and SUM()) and PIVOT in T-SQL (although I used a "manual" construct to build a pivot table instead of the built-in PIVOT syntax).

Sql-server – Sql Where statement dynamic with case

I wouldn't use a case statement. Just use boolean logic

select *
  from table
 where (@duration = 0)
    or (@duration = 1 and duration > 0)
    or (@duration = 2 and duration = 0)

Best Answer

Related Solutions

Sql-server – Create Calculated Fields from Distinct Values in Second Table

Sql-server – Sql Where statement dynamic with case

Related Question