OraFAQ Forum: SQL & PL/SQL » Reverse running sum

Home » SQL & PL/SQL » SQL & PL/SQL » Reverse running sum

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Reverse running sum [message #614506]

Thu, 22 May 2014 09:57

8511TPC
Messages: 2
Registered: May 2014

Junior Member

I have a table which has data like this:

Year  Month  Count
----  -----  -----
2011  1      100
2011  2      150
2011  3      275
2012  1      325
2012  2      450

The count column holds teh cumulative count. How can we get individual counts for each month like this:

Year  Month  Count
----  -----  -----
2011  1      100
2011  2      50
2011  3      125
2012  1      50
2012  2      125

Any help is greatly appreciated. Thanks.

Report message to a moderator

Re: Reverse running sum [message #614509 is a reply to message #614506]

Thu, 22 May 2014 11:32

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Try the LAG() function:

SQL> WITH Mytab (Year1, Month1, Count1)
  2       AS (SELECT 2011, 1, 100 FROM DUAL         UNION ALL
  3           SELECT 2011, 2, 150 FROM DUAL         UNION ALL
  4           SELECT 2011, 3, 275 FROM DUAL         UNION ALL
  5           SELECT 2012, 1, 325 FROM DUAL         UNION ALL
  6           SELECT 2012, 2, 450 FROM DUAL)
  7  SELECT X.*
  8       , Count1 - (LAG (Count1, 1, 0) OVER (ORDER BY Year1, Month1)) AS X_Count
  9    FROM Mytab X;

     YEAR1     MONTH1     COUNT1    X_COUNT
---------- ---------- ---------- ----------
      2011          1        100        100
      2011          2        150         50
      2011          3        275        125
      2012          1        325         50
      2012          2        450        125

[Updated on: Thu, 22 May 2014 11:48] by Moderator

Report message to a moderator

Re: Reverse running sum [message #614542 is a reply to message #614506]

Fri, 23 May 2014 00:35

parthiv_t
Messages: 15
Registered: July 2011
Location: ahmedabad

Junior Member

Hello you can do this via Lag and lead function,which is provide in 10g and higher version

step 1:-
Create table scott.cumsum (year number(4) , month number(2),count number(5));

step 2:-
SET DEFINE OFF;
INSERT INTO SCOTT.CUMSUM (YEAR, MONTH, COUNT)
VALUES (2011, 1, 100);

INSERT INTO SCOTT.CUMSUM (YEAR, MONTH, COUNT)
VALUES (2011, 2, 150);

INSERT INTO SCOTT.CUMSUM (YEAR, MONTH, COUNT)
VALUES (2011, 3, 275);

INSERT INTO SCOTT.CUMSUM (YEAR, MONTH, COUNT)
VALUES (2012, 1, 325);

INSERT INTO SCOTT.CUMSUM (YEAR, MONTH, COUNT)
VALUES (2012, 2, 450);

COMMIT;

step 3:-
select year,month, count ,(count - nvl(prev_count,0)) des_op
from
(select year, month,count,
LAG (count,1) over (ORDER BY year,month ) AS prev_count
from scott.cumsum
)

o/P

year month count des_op
2011 1 100 100
2011 2 150 50
2011 3 275 125
2012 1 325 50
2012 2 450 125

Report message to a moderator

Re: Reverse running sum [message #614546 is a reply to message #614542]

Fri, 23 May 2014 01:00

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is this not what have been provided in the previous post?
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: Reverse running sum [message #614556 is a reply to message #614542]

Fri, 23 May 2014 05:50

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

parthiv_t wrote on Fri, 23 May 2014 01:35

Hello you can do this via Lag and lead function,which is provide in 10g and higher version

Analytic functions LAG/LEAD are available since Oracle 8i.

SY.

Report message to a moderator

Previous Topic:	To create External table for database table
Next Topic:	How to resolve mutation error in function?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:01:17 CDT 2024