0

I have an activity_log table that I need to derive a basic output of how many distinct users logged in that day, and sum up them up day by day. The table logs all actions of a user and will have several entries per day for each user. Here is the table -

Log table

This is what I have so far -

select DISTINCT CAST(test.activitytime AS date) Date, username from [dbo].[activity_log] as test
where test.activitytime between '2024-03-01' and '2024-03-08'
order by Date

Here is the output for that -

output

I need it to display every day once, and sum up the total of distinct users per day (does not matter if they logged in yesterday as well) just total distinct users a day. I am lost on where to go from here

ie

2024-03-01 24

2024-03-02 39

2024-03-03 26

1 Answer 1

0

A distinct count should achieve the result you're looking for.

SELECT CONVERT(date, activitytime) AS [Date],
COUNT(DISTINCT [username]) AS [UserCount]
FROM [dbo].[activity_log]
WHERE CONVERT(date, activitytime) BETWEEN '2024-03-01' and '2024-03-08'
GROUP BY CONVERT(date, activitytime)
ORDER BY 1;
3
  • This is it! Thank you so much. Just for extra credit I'm trying to add a total at the bottom by using GROUP BY ROLLUP. Its not behaving as expected, any ideas?
    – JDBA
    Commented Apr 18 at 12:07
  • @Jeff GROUP BY ROLLUP(CONVERT(date, activitytime)) or GROUP BY GROUPING SET S ( (CONVERT(date, activitytime)), () ) Also don't use WHERE CONVERT(date, activitytime) BETWEEN '2024-03-01' and '2024-03-08', instead use date ranges as then you can hit indexes WHERE activitytime >= '20240301' AND activitytime < '20240309' Also ORDER BY 1 should be avoided, instead do ORDER BY Date Commented Apr 18 at 12:23
  • SELECT CONVERT(date, activitytime) AS [Date], COUNT(DISTINCT [username]) AS [UserCount] FROM [dbo].[activity_log] WHERE CONVERT(date, activitytime) BETWEEN '2024-03-01' and '2024-03-08' GROUP BY ROLLUP (CONVERT(date, activitytime)) ORDER BY Date; returns a row at the top of 541, which does not equal the total of the usercounts.
    – JDBA
    Commented Apr 18 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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