Cumulative sum() every 3 days SQL

Question

I have a table like this

date       amount
2020-02-01    5 
2020-02-02    2 
2020-02-03    10 
2020-02-04    2  
2020-02-06    3 
2020-02-07    1

And I need sum() every 3 days as below:

date       amount   sum
2020-02-01    5      5
2020-02-02    2      7
2020-02-03    10     17
2020-02-04    2      2
2020-02-06    3      5
2020-02-07    1      1
...

So when a difference between days is 3, the summation should start over. Some days may not be in the table.

I tried to do this with window function like sum(amount) over (order by date) but I have no idea how to set a fixed number of days and get the date difference in cumulative sum like this. Is it possible in any SQL?

I've removed the tag spam. Tag spam doesn't help us help you; it makes it harder to. Tagging multiple conflicting tags means that we have no idea what technology you are really asking about, making your question unclear and difficult to answer. It can easily end up attracting downvotes and also close votes if they make the question unclear. Just tag the technologies you are actually asking about. As you state *"Is it possible in any SQL?"* I've added the tag [[tag:database-agnositic]] for now; if you are asking about a specific RDBMS remove that tag and retag the RDBMS you are really using. — Thom A, May 25 '22 at 10:02
You can easily get the sum for the "last 3 days" (like a rolling window) but I am not aware of a feature that resets the sum every three days. — , May 25 '22 at 10:02
If you have a row for every day, then in T-SQL you could limit the range of the window with `ROWS BETWEEN` in the `OVER` clause. Looking at your sample data, however, it seems you don't (as `2020-02-05` is missing). — Thom A, May 25 '22 at 10:03
What do you actually mean by *"Is it possible in any SQL?"* Are you looking for a solution that uses ANSI-SQL in the hope that the query will (hopfully) work in as many RDBMS without changes, or do you mean that you don't mind if the solution is dialect specific? — Thom A, May 25 '22 at 10:05

score 1 · Accepted Answer · answered May 25 '22 at 10:26

1

In MS Sql Server

select t.[date], t.Amount, sum(t.Amount) over(partition by datediff(d, '2020-02-01', t.[date])/3 order by t.[date]) cum
from tbl t

'2020-02-01' is a starting date you want.

answered May 25 '22 at 10:26

Serg

22,285
5
21
48

Thom A · Answer 2 · 2022-05-25T11:35:53.447

Disclaimer
The following solution was written based on a Preview version of SQL Server 2022, and thus may not reflect the final release.

For a bit of fun, if you had access to SQL Server 2022 (which went into preview yesterday) you could use DATE_BUCKET to "round" the date in the PARTITION BY to 3 days, using the minimum date as the starting date.

DECLARE @StartDate date,
        @EndDate date;

SELECT @StartDate = MIN(date),
       @EndDate = MAX(date)
FROM dbo.YourTable;

SELECT date,
       SUM(amount) OVER (PARTITION BY DATE_BUCKET(DAY,3,date,@StartDate) ORDER BY date) AS Amount
FROM dbo.YourTable
WHERE date >= @StartDate
  AND date <= @EndDate; --Incase this would be parametrised

Image of results as expected, as Fiddles of 2022 don't exist:

Cumulative sum() every 3 days SQL

2 Answers2