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

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:

I would like to return

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!

CodePudding user response：

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

Y9ou can even do this in a single level

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