CODEWITHSUNDEEP
sqloracle-databasegroup-bysumconditional-statements
Arturo Sbr
Arturo Sbr

Reputation: 6333

Grouped conditional sum in Oracle SQL

my_table shows the account balance of each person's credits N months ago. From this table, I want to get the monthly sum of each person's balances for the past 2 and 3 months and divide each sum by 2 and 3 respectively (that is, a moving average of the sum of balance for the last 2 and 3 months).

Please note that I need the sum of the balance in the past M months divided by M months.

PERSON_ID  CRED_ID  MONTHS_BEFORE  BALANCE
       01       01              1     1100
       01       01              2     1500
       01       01              3     2000
       01       02              1       50
       01       02              2      400
       01       02              3      850
       02       06              1      300
       02       06              2      320
       02       11              1     7500
       02       11              2    10000

One way to do this would be to:

select
    person_id, sum(balance) / 2 as ma_2
from
    my_table
where
    months_before <= 2
group by
    person_id

and merge this result with

select
    person_id, sum(balance) / 3 as ma_3
from
    my_table
where
    months_before <= 3
group by
    person_id

I want to know if this can be handled with a case or a conditional sum or something along these lines:

select
    person_id,
    sum(balance) over (partition by person_id when months_before <= 2) / 2 as ma_2,
    sum(balance) over (partition by person_id when months_before <= 3) / 3 as ma_3
from
    my_table

The desired result would look as follows:

PERSON_ID     MA_2     MA_3
       01  1525.00  1966.66
       02  9060.00  9060.00

Upvotes: 0

Views: 91

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

If these two queries gives what you want and you need to merge them then only ma_2 needs conditional sum:

select person_id, 
       sum(case when months_before <= 2 then balance end) / 2  as ma_2,
       sum(balance) / 3 as ma_3
  from my_table
  where months_before <= 3
  group by person_id

dbfiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you had a "month" column, you would use a window function:

select t.*,
       avg(balance) over (partition by person_id
                          order by month
                          rows between 2 preceding and current row
                         ) as avg_3month
from t;

Upvotes: 0

Related Questions