CODEWITHSUNDEEP
sqlsql-server
Hans-Rudolf Steiger
Hans-Rudolf Steiger

Reputation: 118

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

Upvotes: 0

Views: 1117

Answers (2)

KeAoneR 93mc
KeAoneR 93mc

Reputation: 1

with redshift database

select [Date],sum([Count]) over(order by [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
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

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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.

Upvotes: 2

Related Questions