calculate running balance in oracle query

Question

I have data like this

id    cp_id   amount_a       amount_b
CCP1  TTP01   10.000.000     2.000.000
CCP1  TTP02   10.000.000     3.000.000
CCP1  TTP03   10.000.000     1.000.000
CCP1  TTP04   10.000.000       500.000
CCP2  TTP05    5.000.000     1.000.000
CCP2  TTP06    5.000.000     2.000.000
CCP3  TTP07    1.000.000       500.000

I want the the result data add one column of running_balance like this below

id       amount_a       amount_b      running_balance
CCP1   10.000.000     2.000.000     8.000.000
CCP1   10.000.000     3.000.000     5.000.000
CCP1   10.000.000     1.000.000     4.000.000
CCP1   10.000.000       500.000     3.500.000
CCP2    5.000.000     1.000.000     4.000.000
CCP2    5.000.000     2.000.000     2.000.000
CCP3    1.000.000       500.000       500.000

I made already the query like this

/* Formatted on 1/26/2017 2:50:06 PM (QP5 v5.115.810.9015) */
  SELECT   B.NO_KLAIM AS id,
           a.amount AS amount_a,
           B.AMOUNT AS amount_b,
           SUM (A.AMOUNT) OVER (ORDER BY B.AMOUNT ROWS UNBOUNDED PRECEDING)
              AS running_balance
    FROM      TRX_TITIPAN A
           JOIN
              TRX_KLAIM_TITIPAN B
           ON A.NO_RESI_TITIPAN = B.NO_RESI_TITIPAN
GROUP BY   B.NO_KLAIM, B.AMOUNT, a.amount

but the result not calculated amount_a, just running total for amount_b.

**Updated: I make already update on my query.

 /* Formatted on 1/26/2017 2:50:06 PM (QP5 v5.115.810.9015) */
      SELECT   B.NO_KLAIM AS id,
               a.amount AS amount_a,
               B.AMOUNT AS amount_b,
               NVL (TITIP.AMOUNT, 0)
               - SUM (NVL (KLAIM.AMOUNT, 0))
                    OVER (PARTITION BY TITIP.AMOUNT
                          ORDER BY TITIP.NO_RESI_TITIPAN,
                                   KLAIM.NO_KLAIM,
                                   TITIP.AMOUNT,
                                   KLAIM.AMOUNT asc
                          ROWS UNBOUNDED PRECEDING) as running_balance
        FROM      TRX_TITIPAN A
               JOIN
                  TRX_KLAIM_TITIPAN B
               ON A.NO_RESI_TITIPAN = B.NO_RESI_TITIPAN
    GROUP BY   B.NO_KLAIM, B.AMOUNT, a.amount

the running balance is working right when using WHERE id= .... condition, with specific id. While i delete WHERE id= .... condition it's going wrong.

For your additional requirement, you need to include id in the partition by` clause. `Partition by` in an analytic function works in the same way that `Group by` does in an aggregate query - it determines the groups of rows that you want the analytic function to work across. — Boneist, Feb 06 '17 at 08:16

score 5 · Answer 1 · edited Jan 26 '17 at 09:30

5

I think you're after:

SELECT   B.NO_KLAIM AS id,
         a.amount AS amount_a,
         B.AMOUNT AS amount_b,
         a.amount - SUM (B.AMOUNT) 
                   OVER (partition by b.no_klaim ORDER BY B.cp_id ROWS UNBOUNDED PRECEDING)
              AS running_balance
FROM      TRX_TITIPAN A
           JOIN
              TRX_KLAIM_TITIPAN B
           ON A.NO_RESI_TITIPAN = B.NO_RESI_TITIPAN

edited Jan 26 '17 at 09:30

APC

144,005
19
170
281

answered Jan 26 '17 at 08:08

Boneist

22,910
1
25
40

The raw data contains an additional identifier, `cp_id`, which looks like it determines the order of the withdrawals. So it should be used as the ORDER BY criterion in the analytic clause. – APC Jan 26 '17 at 09:33
I was going from the OP's original proposed query where they ordered by the b.amount (although I hadn't spotted that I and the OP had missed the DESC keyword in the sort). – Boneist Jan 26 '17 at 09:51
1

Sorting by `amount desc` wouldn't explain the order for `id= 'CCP2'` but sorting by `cp_id` would. – APC Jan 26 '17 at 10:28

score 4 · Answer 2 · answered Jan 30 '17 at 10:11

It would be much easier if you attach description of your tables.

From sample data you provided you need:

select id, amout_a, amount_b, 
       amount_a - sum(amount_b) over (partition by id order by id, cp_id) as running_balance 
  from table;

Trying to translate it into your tables it gives:

SELECT B.NO_KLAIM AS id,
       a.amount AS amount_a,
       B.AMOUNT AS amount_b,
       a.amount - SUM(B.AMOUNT) OVER (PARTITION BY B.NO_KLAIM ORDER BY B.NO_KLAIM/*, HERE PUT WHAT IS cp_id*/) AS running_balance
FROM      TRX_TITIPAN A
       JOIN
          TRX_KLAIM_TITIPAN B
       ON A.NO_RESI_TITIPAN = B.NO_RESI_TITIPAN

You just need to fill one column in order by which stands for cp_id

calculate running balance in oracle query

2 Answers2

Linked

Related