Cumulative sum which is grouped by

Question

I'm looking for a solution where I can use a cumulative sum in combination with a group by.

I have the following table:

office_id	time	revenue
1	2022-01-01 12:00:00	100
1	2022-01-02 13:10:00	50
1	2022-01-02 17:00:00	40

Using the following query, I can get the cumulative sum for each entry:

SELECT office_id, date_trunc('day', time) as ts, sum(revenue) over (partition by office_id order by time) as cum_rev FROM business.revenue ORDER BY office_id, ts;

Which gives me:

office_id	ts	cum_rev
1	"2022-01-01 00:00:00"	100
1	"2022-01-02 00:00:00"	150
1	"2022-01-02 00:00:00"	190

What I want to obtain is to group the data on the truncated time, ie:

office_id	ts	cum_rev
1	"2022-01-01 00:00:00"	100
1	"2022-01-02 00:00:00"	190

What changes to my query do I have to make to get this result? I have a feeling I should group by my ts field but it's not that easy unfortunately.

how did office_id change from 1,2,3 to 1,1,1? You won't get a running total if you partition by office_id and you have three different ones. — DannySlor
– DannySlor, Commented Sep 13, 2022 at 2:09
@DannySlor That's my mistake in typing over the test table data, I've corrected it! — Michael Anckaert
– Michael Anckaert, Commented Sep 13, 2022 at 2:31

jjanes · Accepted Answer · 2022-09-13 02:09:51Z

(Assuming the office_id were supposed to be all 1s)

You need two levels of nesting, one to do the grouping sum and one the cumulation sum:

select office_id, ts, sum(sum) over (partition by office_id order by ts) from ( SELECT office_id, date_trunc('day', time) as ts, sum(revenue) FROM revenue GROUP BY 1, 2 ) foo ORDER BY office_id, ts;

DannySlor · Accepted Answer · 2022-09-13 02:11:04Z

select office_id ,ts ,sum(rev) over(order by ts) as cum_rev from ( select office_id ,date_trunc('day', time) as ts ,sum(revenue) as rev from t group by office_id, date_trunc('day', time) ) t

office_id	ts	cum_rev
1	2022-01-01 00:00:00	100
1	2022-01-02 00:00:00	190

Fiddle

score 1 · Accepted Answer · 2022-09-13 02:24:05Z

I assumed 1, 2, 3 was supposed to be 1's

I just summed over the grain it sounds like you wanted, id and then just added a distinct to remove duplicate dates, but the aggregate will give you the total mount over id

SELECT DISTINCT office_id, date_trunc('day', time) as ts, sum(revenue) over (partition by office_id order by date_trunc('day', time)) as cum_rev FROM business.revenue ORDER BY ts;

Collectives™ on Stack Overflow

Cumulative sum which is grouped by

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related