Cumulative count of SQL Server users over several days
I have a table user
like this:
id | createdAt | name ...
---+--------------------------------+-----------
1 2017-04-12 13:19:37.287 +00:00 X
2 2017-04-12 13:19:37.287 +00:00 Y
3 2017-04-12 13:19:37.287 +00:00 Z
4 2017-04-13 13:19:37.287 +00:00 T
5 2017-04-13 13:19:37.287 +00:00 W
6 2017-04-14 13:19:37.287 +00:00 W
7 2017-04-14 13:19:37.287 +00:00 W
What I need is the cumulative number of users per day, for example:
day | count
-----------+--------
2017-04-12 3
2017-04-13 5
2017-04-14 7
I have tried grouping the days with this SQL statement, but it only gives me day entries not cumulative
SELECT
CAST(createdAt AS DATE) AS date, COUNT(*) AS userCount
FROM
dbo.Users
GROUP BY
CAST(createdAt AS DATE)
ORDER BY
CAST(createdAt AS DATE) DESC
Any help is appreciated
thank
+3
source to share
1 answer
In SQL Server 2012+, you must use a cumulative sum:
SELECT CAST(createdAt AS DATE) as date, COUNT(*) as userCount,
SUM(COUNT(*)) OVER (ORDER BY CAST(createdAt AS DATE)) as cumulative_count
FROM dbo.Users u
GROUP BY CAST(createdAt AS DATE)
ORDER BY CAST(createdAt AS DATE) DESC;
In earlier versions, some other method is needed, such as a correlated subquery:
WITH u as (
SELECT CAST(createdAt AS DATE) as date, COUNT(*) as userCount
FROM dbo.Users u
GROUP BY CAST(createdAt AS DATE)
)
SELECT u.*,
(SELECT SUM(usrCount)
FROM u u2
WHERE u2.date <= u.date
) as cumulative_count
FROM u
ORDER BY date DESC;
+7
source to share