Home » SQL & PL/SQL » SQL & PL/SQL » sum the next both tuples
icon4.gif  sum the next both tuples [message #250567] Tue, 10 July 2007 08:52 Go to next message
ddaannyy
Messages: 13
Registered: March 2007
Junior Member
Hi

i think i can descriebe my problem easy with an example;

i have like this:

01.01.1999 0
01.01.1999 200
02.01.1999 180
03.01.1999 -30
04.01.1999 60

and i want like this

01.01.1999 0 null
01.01.1999 200 200
02.01.1999 180 380
03.01.1999 -30 350
04.01.1999 60 410


rollup doesn't work because i have nothing for group by :0(


Re: sum the next both tuples [message #250571 is a reply to message #250567] Tue, 10 July 2007 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
select t.*,
       val-lag(val) over(order by mydate) newone
from t
/

Of course if you followed the sticky we could answer more accurately.

Regards
Michel
Re: sum the next both tuples [message #250582 is a reply to message #250567] Tue, 10 July 2007 09:52 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
select t.*,
       sum(t.val) over(order by indate) total
from tab t
Re: sum the next both tuples [message #250595 is a reply to message #250582] Tue, 10 July 2007 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is much better. I stuck on the first lines.
This is why wording the problem AND posting an example are BOTH mandatory.

Regards
Michel
Re: sum the next both tuples [message #250738 is a reply to message #250567] Wed, 11 July 2007 02:12 Go to previous messageGo to next message
ddaannyy
Messages: 13
Registered: March 2007
Junior Member
Thanks it works fine,

but i have one question more Surprised), but this is really very difficult.

example:

date       X   Y   Z
--------   -   -   -   -----
1.1.1999   0   1   0   null
2.1.1999   0   1   10  10
3.1.1999   0   1   -3  7
4.1.1999   0   1   4   11
5.1.1999   1   1   25  25
6.1.1999   0   1   -11 14


Here he stould start count new, always x=1. Is such possible as sql statement?

[Updated on: Wed, 11 July 2007 02:27] by Moderator

Report message to a moderator

Re: sum the next both tuples [message #250747 is a reply to message #250738] Wed, 11 July 2007 02:34 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Something like this might work:
SQL> WITH yourtable AS
  2  (
  3    SELECT TO_DATE('1.1.1999','dd.mm.yyyy') thedate, 0 x,  1 y,  0   z
  4    FROM dual
  5    UNION ALL
  6    SELECT TO_DATE('2.1.1999','dd.mm.yyyy') thedate, 0 x,  1 y,  10  z
  7    FROM dual
  8    UNION ALL
  9    SELECT TO_DATE('3.1.1999','dd.mm.yyyy') thedate, 0 x,  1 y,  -3  z
 10    FROM dual
 11    UNION ALL
 12    SELECT TO_DATE('4.1.1999','dd.mm.yyyy') thedate, 0 x,  1 y,  4   z
 13    FROM dual
 14    UNION ALL
 15    SELECT TO_DATE('5.1.1999','dd.mm.yyyy') thedate, 1 x,  1 y,  25  z
 16    FROM dual
 17    UNION ALL
 18    SELECT TO_DATE('6.1.1999','dd.mm.yyyy') thedate, 0 x,  1 y,  -11 z
 19    FROM dual
 20  )
 21  SELECT thedate
 22       , x
 23       , y
 24       , z
 25       , SUM(z) OVER ( PARTITION BY gr ORDER BY thedate ) sm
 26  FROM   ( SELECT thedate
 27                , x
 28                , y
 29                , z
 30                , SUM(x) OVER ( ORDER BY thedate) gr
 31           FROM   yourtable
 32         )
 33  ORDER  BY thedate
 34  /

THEDATE            X          Y          Z         SM
--------- ---------- ---------- ---------- ----------
01-JAN-99          0          1          0          0
02-JAN-99          0          1         10         10
03-JAN-99          0          1         -3          7
04-JAN-99          0          1          4         11
05-JAN-99          1          1         25         25
06-JAN-99          0          1        -11         14

6 rows selected.

SQL>


MHE
icon8.gif  Re: sum the next both tuples [message #250749 is a reply to message #250567] Wed, 11 July 2007 02:51 Go to previous messageGo to next message
ddaannyy
Messages: 13
Registered: March 2007
Junior Member
yep ok, that was my idea too, but for 3 5 years is this way impossible
Sad
Re: sum the next both tuples [message #250750 is a reply to message #250749] Wed, 11 July 2007 02:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ddaannyy wrote on Wed, 11 July 2007 09:51
yep ok, that was my idea too, but for 3 5 years is this way impossible
Sad

Explain why.

MHE
Re: sum the next both tuples [message #250759 is a reply to message #250567] Wed, 11 July 2007 03:34 Go to previous messageGo to next message
ddaannyy
Messages: 13
Registered: March 2007
Junior Member
i'm shame Embarassed

yep are absolut right, it works, i made a mistake.

big sorry and big big thanks for your help Razz


Re: sum the next both tuples [message #250916 is a reply to message #250747] Wed, 11 July 2007 15:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Very nice solution Maarten! Great idea to use the sum(x) over () as a grouping field!!!
Previous Topic: updating 500,000 records 3,000 at a time
Next Topic: message displaying
Goto Forum:
  


Current Time: Sun Dec 04 16:46:44 CST 2016

Total time taken to generate the page: 0.06781 seconds