CODEWITHSUNDEEP
sqlpostgresql
youwenti
youwenti

Reputation: 99

How to calculate sum of current week for every row in sql?

        update_time         | daily_profit 
----------------------------+---------------------------
 2021-02-26 00:33:00.13055  |    0.1
 2021-02-27 14:23:18.173233 |    0.2
 2021-02-28 00:33:00.13055  |    0.4
 2021-03-01 01:31:00.146775 |    0.2

I have a postgres table profits as above, one row one day.

How can I calculate the sum of current week for every row in sql? Like the output as below:

        update_time         |    running_total
----------------------------+---------------------------
 2021-02-26 00:33:00.13055  |    0.1
 2021-02-27 14:23:18.173233 |    0.3   // 0.1 + 0.2
 2021-02-28 00:33:00.13055  |    0.4   // 0.4, new week 
 2021-03-01 01:31:00.146775 |    0.6   // 0.4 + 0.2

The question may turn into another question, how to pass a value of current row to sub query?

select update_time,
(select sum(daily_profit) from profits where update_time > (select DATE_TRUNC('WEEK', <update_time_of_current_row>)) and update_time < (select DATE_TRUNC('WEEK', <update_time_of_current_row>) + INTERVAL '7 DAY'))
from profits;

Upvotes: 0

Views: 1226

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use window functions:

select p.*,
       sum(daily_profit) over (partition by date_trunc('week', update_time) order by update_time) as running_weekly_profit
from profits p;

Here is a db<>fiddle.

The above starts the week on Monday, which is pretty standard. If you want Sunday:

select p.*,
       sum(daily_profit) over (partition by date_trunc('week', update_time + interval '1 day') order by update_time) as running_weekly_profit
from profits p;

Upvotes: 2

Related Questions