Home » SQL & PL/SQL » SQL & PL/SQL » How to reset the culmalative balance
How to reset the culmalative balance [message #191528] Wed, 06 September 2006 23:07 Go to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

Dear sir,


Below is my sql scripts and the output.


select part,date_tran, ,QTY, sum(QTY)over(order by rownum)
from abc

order by rownum

Output
------------

123 01-AUG-06 3 3
123 04-AUG-06 -2 1
123 08-AUG-06 8 9
456 10-AUG-06 4 13
456 15-AUG-06 2 15



The correct output that i need is

Correct Output
---------------
123 01-AUG-06 3 3
123 04-AUG-06 -2 1
123 08-AUG-06 8 9
456 10-AUG-06 4 4
456 15-AUG-06 2 6



Please help to check. Thank

Mabel
Re: How to reset the culmalative balance [message #191538 is a reply to message #191528] Thu, 07 September 2006 01:15 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


 

SELECT PART,DATE_TRAN,QTY,SUM(QTY)OVER(PARTITION BY PART ORDER BY DATE_TRAN)CUM_SUM
FROM TEST4 



regards,
Re: How to reset the culmalative balance [message #191557 is a reply to message #191528] Thu, 07 September 2006 02:06 Go to previous message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

hi

try this

SELECT part, date_tran, QTY,
( SELECT SUM(a2.qty)
FROM abc a2
WHERE a2.part = a1.part
AND a2.ROWID <= a1.ROWID) Running_Total
FROM abc a1
ORDER BY part

@dhananjay:
plz review your query for the case with duplicate DATE_TRAN

Thks & Rgds
Venkat

[Updated on: Thu, 07 September 2006 02:14]

Report message to a moderator

Previous Topic: diff rowid & rownum
Next Topic: How to handle BLOB data
Goto Forum:
  


Current Time: Sat Dec 03 00:53:01 CST 2016

Total time taken to generate the page: 0.05013 seconds