CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Nidheesh
Nidheesh

Reputation: 812

sql query to calculate sum and add sum from previous rows

This is my table T1

  dat_dt      credit     debit
 --------     ------     -----
22-02-2019      10         5
25-02-2019      30         60
10-03-2019      50         40
13-03-2019      100        10

I want to generate the following output

max_date    cr_sum    progressive credit  dr_sum progressive debit 
--------    ------    ------------------  ------  -----------------
25-02-2019    40            40              65           65
13-03-2019    150           190             50           115

I want to calculate sum of credit and debit in each month, the progressive credit is the column which is sum of credit in that month added to progressive credit of previous month. same for progressive debit. max_date is the maximum date in each month. Is there any simple method to get this output other than using loops?

Upvotes: 2

Views: 1239

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can use the following query 

with t1(  dat_dt, credit, debit) as
(
 select date'2019-02-22',10,  5 from dual union all
 select date'2019-02-25',30, 60 from dual union all
 select date'2019-03-10',50, 40 from dual union all
 select date'2019-03-13',100,10 from dual    
)
select max(dat_dt) as "max date",
       sum(credit) as "cr sum",
       sum(sum(credit)) over ( order by to_char(dat_dt,'yyyymm') ) as "progressive credit", 
       sum(debit) as "dr sum",
       sum(sum(debit))  over ( order by to_char(dat_dt,'yyyymm') ) as "progressive debit"
  from t1
 group by to_char(dat_dt,'yyyymm')
 order by to_char(dat_dt,'yyyymm');

with aggregation by year-month combination.

Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You seem to want a cumulative sum, but aggregated by month:

select max(dat_dt) as dat_dt,
       sum(credit) as credit,
       sum(sum(credit)) over (order by min(dat_dt)) as running_credit,
       sum(debit) as debit,
       sum(sum(debit)) over (order by min(dat_dt)) as running_debit
from t1
group by to_char(dat_dt, 'YYYY-MM')
order by min(dat_dt);

Here is a db<>fiddle.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below -

select max(dat_date) as max_date,sum(credit) as cr_sum,
       sum(credit) over(order by EXTRACT(month FROM dat_date),EXTRACT(year FROM dat_date)) as progressive_credit,
       sum(debit) as dr_sum,
       sum(debit) over(order by EXTRACT(month FROM dat_date),EXTRACT(year FROM dat_date)) as progressive_debit
group by EXTRACT(month FROM dat_date),EXTRACT(year FROM dat_date)

Upvotes: 1

Related Questions