SQL Server Running total in SQL based on date

qv7cva1a 于 10个月前发布在其他

关注(0)|答案(1)|浏览(115)

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!

sql-server

来源：https://stackoverflow.com/questions/69761159/running-total-in-sql-based-on-date

1条答案

按热度按时间

x8goxv8g1#

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

赞(0）回复(0）举报 10个月前

我来回答

SQL Server Running total in SQL based on date

1条答案

相关问题

热门标签

最新问答