Running total in SQL based on date

Question

So I have the following two columns which I would like to create a running total column by date in SQL - here is a small snippet

creation date	is_registered
2021-03-30	1
2021-03-30	1
2021-03-31	1
2021-03-31	1

The issue I seem to have is the date has the time stamp and the reg column is in bit format, so I tried the following query

with reg as(      
    select
        cast([created_at] as date) as 'date',
        sum(cast([is_registered] as int)) as 'sum_of_reg'
    FROM [dbo].[Registrations]
    group by [created_at]
)
select
    [date],
    sum_of_reg,
    sum(sum_of_reg) over (order by [date]) as 'running total'
FROM reg
group by [date], sum_of_entries
order by [date] asc

However this returns the following:

date	sum of reg	running total
2021-03-30	1	1
2021-03-30	1	1
2021-03-31	2	3

I would like to return

date	sum of reg	running total
2021-03-30	2	1
2021-03-31	2	3

Rather than combining the date into one distinct value, it still shows the same date twice.

I think it still treating the date separately due to the timestamps, but not sure of a way around it

Any advice would be much appreciated!

Answer 1

You have the wrong grouping clause in the reg CTE, you need cast([created_at] as date).

The outer group by is not necessary

The default window in an OVER clause (when there is an ORDER BY) is unfortunately RANGE UNBOUNDED PRECEDING, which is rarely what people expect.
You must specify ROWS UNBOUNDED PRECEDING explicitly.

with reg as(      
    select
        cast([created_at] as date) as [date],
        sum(cast([is_registered] as int)) as [sum_of_reg]
    FROM [dbo].[Registrations]
    group by cast([created_at] as date)
)
select
    [date],
    sum_of_reg,
    sum(sum_of_reg) over (order by [date] ROWS UNBOUNDED PRECEDING) as [running total]
FROM reg
order by [date] asc

You can even do this in a single level, by using a window function on top of normal aggregation.

select
    cast([created_at] as date) as [date],
    sum(cast([is_registered] as int)) as [sum_of_reg],
    sum(sum(cast([is_registered] as int)))
      over (order by [date] ROWS UNBOUNDED PRECEDING) as [running total]
FROM [dbo].[Registrations]
group by cast([created_at] as date)
order by [date] asc