1

i have sample data in oracle data table namely STOKREWARD table, i wanna get balance between quantity IN and quantity OUT.

enter image description here

i wanna get value BALANCES like this:

enter image description here

i try using query like this, but the result doesn't i want

SELECT STOKREWARD.NO, STOKREWARD.DODATE, STOKREWARD.REWARDNAME, STOKREWARD.NOTES,
STOKREWARD.QTYIN, STOKREWARD.QTYOUT,
(STOKREWARD.QTYIN- STOKREWARD.QTYOUT) AS BALANCES
FROM STOKREWARD
ORDER BY STOKREWARD.NO ASC

this query gives result:

enter image description here

anyone can help me? thanks

aminvincent
  • 553
  • 1
  • 12
  • 43

1 Answers1

1

Using SUM() OVER:

SELECT NO, DODATE, CODE, REWARDNAME, NOTES, QTYIN, QTYOUT,
    SUM (QTYIN - QTYOUT) OVER (ORDER BY DODATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BALANCES
FROM STOKREWARD
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • can u explain me what is the meaning of `SUM (QTYIN - QTYOUT) OVER (ORDER BY DODATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)` ? – aminvincent Jun 29 '16 at 06:46
  • This adds the previous balance to the current balance, for each row, and does this for all records in your table. It is a running sum, q.v. [here](http://stackoverflow.com/questions/1092120/over-clause-in-oracle) for more information. – Tim Biegeleisen Jun 29 '16 at 06:49