running sum based in dates

Question

running sum based in dates

Shambhu Rai 1,411

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)

Guoxiong 8,206 Reputation points

2022-03-21T14:24:11.607+00:00

Better to post more sample data and the expected output?

Accepted answer

1 additional answer

Your answer

Guoxiong 8,206 Reputation points

2022-03-21T14:24:11.607+00:00

Better to post more sample data and the expected output?

Answer 1

Guoxiong 8,206

As @Olaf Helper mentioned, you can use the SUM() OVER ... statement to get your sales running total:

DECLARE @Table TABLE (  
	Saled_Date date,  
	Sales_Count int  
);  
INSERT INTO @Table VALUES ('2022-02-23', 2499), ('2022-03-03', 1);  
  
SELECT *, SUM(Sales_Count) OVER (ORDER BY Saled_Date) AS RunningTotal   
FROM @Table;

Answer 2

Olaf Helper 47,436

In T-SQL your can use the OVER clause with SUM as aggregation, see https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15 => Example B. Using the OVER clause with aggregate functions

Shambhu Rai 1,411 Reputation points

2022-03-21T12:23:28.973+00:00

Hi Expert,

but it is showing countsale and not sum
Steven Cook 1 Reputation point

2023-01-06T15:44:49.787+00:00

Suppose you had a few more dates and sales between 2-23 and 03-03 and your front end was applying a date range. Your end user wanted to know the cumulative sales as of 03-01 but you didn't have any new sales for that date (but did for 02-24 and 02-27 and none for any other dates). What's the best way to query the cumulative sales for a date where you had no sales?

Share via

running sum based in dates

1 additional answer

Your answer