running sum based on date
-
Hi Expert,
i wanted to calculate running sum based on dates and that would be 3rd column. how can i do this in sql server
create table main3( saleddate date, sales_count int)
insert [main3]
values('2022-03-03',1),
('2022-02-23',2499)
Expected result
-
you can use the sum function with over clause. Here is an example that is based on your script:
--create thet table
create table main3( saleddate date, sales_count int)
go
--insert some data
insert [main3]
values('20220303',1),
('20220223',2499),
('20220310', 500)
go
--run the query
select saleddate, sales_count, sum(sales_count) over (order by saleddate) as RunningTotal
from main3
go
--clean it up:-)
drop table main3
goAdi
-
it shows multiple dates for values which is incorrect
-
Add a group by
GROUP BY saleddate, sales_count
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
You can use a CTE that first sums up the data according to the date, and then run the query on the CTE:
create table main3( saleddate date, sales_count int)
go
--insert some data
insert [main3]
values('20220303',1),
('20220223',2499),
('20220310', 500),
('20220310', 100)
go
--run the query
with MyCTE as (
select saleddate, sum(sales_count) as sales_count
from main3
group by saleddate)
select saleddate, sales_count, sum(sales_count) over (order by saleddate) as RunningTotal
from MyCTE
go
--clean it up:-)
drop table main3
goAdi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply