CODEWITHSUNDEEP
postgresql
user13641365
user13641365

Reputation:

Cumulative Sum of 2 Columns

I am using Postgresql 11.

I have 2 tables - txn_table and summary_table

create table txn_table(id int, txn_date timestamp, amount decimal);

create table summary_table(id int, txn_date date, day_Total decimal, Cumulative_Total decimal);

Sample data for txn_table is

insert into txn_table values (1, '2020-05-28 10:05:05', 100.00);
insert into txn_table values(2, '2020-05-28 11:45:10', 200.00);
insert into txn_table values(3, '2020-05-29 10:05:05', 300.00);
insert into txn_table values(4, '2020-05-29 12:10:01', 400.00);

I want to insert the data in summary_table like below

  1. day_total contains sum of particular day
  2. Cumulative_total contains cumulative sum of day_total
| id  | txn_date                 | day_total | cumulative_total |
| --- | ------------------------ | --------- | ---------------- |
| 1   | 2020-05-28               | 300       | 300              |
| 2   | 2020-05-29               | 700       | 1000             |

Upvotes: 0

Views: 57

Answers (2)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

do following:

Add unique constraint to txn_date column of summary_table. 

create table summary_table(id SERIAL PRIMARY KEY, txn_date date unique, day_total decimal, cumulative_total decimal);

if you want to ignore the duplicates. use below mentioned query

insert into summary_table(txn_date,day_total,cumulative_total)
(select  txn_date, day_total, sum(day_total) over (order by txn_date) as cumulative_total from (
  select txn_date::date as txn_date, sum(amount) as day_total
  from txn_table
  group by txn_date::date
) d 
order by txn_date)
on conflict(txn_date)
do nothing;

if you want to update the duplicate values in summary_table. use below mentioned query

insert into summary_table(txn_date,day_total,cumulative_total)
(select  txn_date, day_total, sum(day_total) over (order by txn_date) as cumulative_total from (
  select txn_date::date as txn_date, sum(amount) as day_total
  from txn_table
  group by txn_date::date
) d 
order by txn_date)
on conflict(txn_date)
do update set day_total=EXCLUDED.day_total,
cumulative_total=EXCLUDED.cumulative_total;

Upvotes: 0

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

I guess this select could solve your problem:

insert into summary_table
select row_number() over w as id
     , txn_date
     , day_total
     , sum(day_total) over w as cumulative_total
from (
  select txn_date::date as txn_date, sum(amount) as day_total
  from txn_table
  group by txn_date::date
) d
window w as (order by txn_date)
order by txn_date

(I worked it out in my head, didn't try. Perhaps prepare db fiddle or CTE with sample input.)

Upvotes: 1

Related Questions