Question 114675 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Cumulative sum which is grouped by

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.

Tags:

3

Answers


  1. 0

    (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;
    Login or Signup to reply.
  2. 0 
    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

    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search