I am new to sql, Experts help required. My current schema:

I want to subtract or add from previous row's total and get last row total calculated properly as show below I am interested in last two row for each transaction.

Desired Output:

CodePudding user response：

You can use SUM( ~ ) OVER ( ~ )

SELECT
  USER_,
  TRANS,
  CREDIT,
  DEBIT,
  sum(CREDIT-DEBIT) over (partition by USER_ order by TRANS) as running_total
FROM table_name
ORDER BY
  USER_,
  TRANS;

USER_ | TRANS | CREDIT | DEBIT | RUNNING_TOTAL
:---- | ----: | -----: | ----: | ------------:
U1111 |     1 |   2000 |     0 |          2000
U1111 |     2 |   4000 |     0 |          6000
U1111 |     3 |      0 |   500 |          5500
U1111 |     4 |   5000 |     0 |         10500
U2222 |     1 |   1000 |     0 |          1000
U2222 |     2 |      0 |   500 |           500
U2222 |     3 |      0 |   200 |           300

db<>fiddle here

CodePudding user response：

You most probably shouldn't store total at all, but calculate it when you need it. Anyway, here's one option - sum function in its analytic form.

Sample data:

SQL> select * from test order by cuser, trans;

CU      TRANS     CREDIT      DEBIT      TOTAL
-- ---------- ---------- ---------- ----------
u1          1       2000          0          0
u1          2       4000          0          0
u1          3          0       1000          0
u1          4       5000          0          0
u2          1       1000          0          0
u2          2          0        500          0
u2          3          0        200          0

7 rows selected.

This is query that returns running total:

SQL> select cuser, trans, credit, debit,
  2    sum(credit - debit) over (partition by cuser order by trans) as total
  3  from test
  4  order by cuser, trans;

CU      TRANS     CREDIT      DEBIT      TOTAL
-- ---------- ---------- ---------- ----------
u1          1       2000          0       2000
u1          2       4000          0       6000
u1          3          0       1000       5000
u1          4       5000          0      10000
u2          1       1000          0       1000
u2          2          0        500        500
u2          3          0        200        300

7 rows selected.

To update the total column, use e.g. merge:

SQL> merge into test a
  2  using (select cuser, trans, credit, debit,
  3           sum(credit - debit) over (partition by cuser order by trans) as total
  4         from test
  5        ) b
  6  on (a.cuser = b.cuser and
  7      a.trans = b.trans
  8     )
  9  when matched then update set
 10    a.total = b.total;

7 rows merged.

Result:

SQL> select * from test order by cuser, trans;

CU      TRANS     CREDIT      DEBIT      TOTAL
-- ---------- ---------- ---------- ----------
u1          1       2000          0       2000
u1          2       4000          0       6000
u1          3          0       1000       5000
u1          4       5000          0      10000
u2          1       1000          0       1000
u2          2          0        500        500
u2          3          0        200        300

7 rows selected.

SQL>