Hans-Rudolf Steiger Hans-Rudolf Steiger - 1 month ago 6
SQL Question

SQL count per day and add together all following days

I'm trying to write a SQL query to count users per day and add the result together. I'm stuck because I don't know how to dynamically add the results together.

Table:

UserId | CreateDate
-------+----------------------------
1 | 2016-06-23 13:59:24.0000000
2 | 2016-06-23 15:59:24.0000000
3 | 2016-06-24 05:59:24.0000000
...


I have following query to count the registrations per day.

SELECT
CONVERT(date, u.CreateDate) as [Date], Count(UserId) as [Count]
FROM
User as u
GROUP BY
CONVERT(date, u.CreateDate)
ORDER BY
[Date]


I have no idea how to add up the results. I hope that SQL Server 2008 R2 has a built-in function for my case. Or do I need to create temp tables?

Assuming Day 1 has 2 registrations and Day 2 has 1 registration and Day 3 has 4 registrations, I would like to receive a result like this:


2016-06-23 | 2
2016-06-24 | 2 + 1 = 3
2016-06-25 | 3 + 4 = 7


I would need a dynamic solution because the query will have another restriction by date => Where CreateDate bewteen x and y.

Thanks
Ruedi

vkp vkp
Answer

You can use sum window function to get the running total.

select [Date],sum([Count]) over(order by [Date]) 
from (SELECT CONVERT(date, u.CreateDate) as [Date], Count(UserId) as [Count] 
      FROM User as u
      GROUP BY CONVERT(date, u.CreateDate)
      ) x
order by 1

Add a where clause to filter for a specific date range in the inner query.