SQL Filter query if column has more than one distinct value

1006 views sql
5

I have a query that returns Date Username and Download Count of a user if download counts exceeds 2.

SELECT CONVERT(DATE, [datetime]) 'Date', 
       actionby                  AS 'Username', 
       Count(*)                  'Download Count' 
FROM   [dbo].[log] 
WHERE  actiontaken = 'Download' 
       AND type = 'Document' 
       AND CONVERT(DATE, [datetime]) BETWEEN 
           CONVERT(DATE, 
           '" + scanStartDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') 
           AND 
CONVERT(DATE, '" + scanEndDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') 
GROUP  BY CONVERT(DATE, [datetime]), 
          actionby 
HAVING Count(*) > 1 
ORDER  BY Count(*) DESC

Below is the returned table

+------------+----------+----------------+
|    Date    | Username | Download Count |
+------------+----------+----------------+
| 01/01/2018 | Jane     |              3 |
+------------+----------+----------------+

Jane did 3 downloads.

But 2 of them are the same file (identified by fileNumber).

How can I change my query so that it will not add in duplicated downloads of any fileNumber on that date?

e.g. Jane downloaded file1 once, and file2 twice. Download Count will count 2 instead of 3.

Thank you

answered question

1 Answer

1

Use fileNumber column in group by and select list

SELECT CONVERT(DATE, [datetime]) 'Date', 
       actionby                  AS 'Username', 
       filenumber, 
       Count(*)                  'Download Count' 
FROM   [dbo].[log] 
WHERE  actiontaken = 'Download' 
       AND type = 'Document' 
       AND CONVERT(DATE, [datetime]) BETWEEN 
           CONVERT(DATE, 
           '" + scanStartDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') 
           AND 
CONVERT(DATE, '" + scanEndDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') 
GROUP  BY CONVERT(DATE, [datetime]), 
          actionby, 
          filenumber 
HAVING Count(*) > 1 
ORDER  BY Count(*) DESC 

posted this

Have an answer?

JD

Please login first before posting an answer.