Michel Cadot schrieb:
> <dead.man.walking_at_gmx.de> a écrit dans le message de news: 1159370526.506812.111470_at_m7g2000cwm.googlegroups.com...
> | Thank you for giving me this hint.
> | But unfortunatelly, it didn't work out for me.
> | Im managed to sum up the values but now EVERY line shows the total over
> | all months.
> | That's not the way I wanted it to be...
> |
> | Any other tips?
> |
> | Alex Sauer
> |
> |
> | Robert Klemme schrieb:
> |
> | > On 27.09.2006 15:18, dead.man.walking_at_gmx.de wrote:
> | > > Hello NG!
> | > >
> | > > Looks like I can't figure out how to do the following in SQL on Orcl
> | > > 10g:
> | > > I'd like to do a summation of values from the 1st of jan. to a give
> | > > month.
> | > >
> | > > Given the following table:
> | > > Month | Value
> | > > ----------|----------
> | > > Jan | 1000
> | > > Feb | 2000
> | > > Mar | 800
> | > > Apr | 600
> | > > May | 1400
> | > > Jun | 730
> | > > Jul | 970
> | > > ...
> | > >
> | > > I want to select MONTH, VALUE, TILL_MONTH_VALUE
> | > > Month | Value | Till_month_value
> | > > ----------|----------|--------------------------
> | > > Jan | 1000 | 1000
> | > > Feb | 2000 | 3000
> | > > Mar | 800 | 3800
> | > > ...
> | > >
> | > > I heard of so-called "domain-functions" in MS-Access, which migth be
> | > > able to do the trick, but which also are rather slow...
> | > >
> | > > So I'd like to know wether there is a passibillity to do the summation
> | > > in a query (within reasonable time) or if I should rather use a table /
> | > > mat. view for storing the sums.
> | > >
> | > > Any ideas and opinions will be appreciated
> | > > Alex Sauer
> | >
> | > Look into "Analytical Functions":
> | > http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm#sthref1924
> | > http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i81407
> | >
> | > Kind regards
> | >
> | > robert
> |
>
> Don't top-post.
>
> You did not read enough the doc. It's basic analytical functions.
>
> SQL> select month, value,
> 2 sum(value) over (order by to_date(month,'Mon')) till_month_value
> 3 from t
> 4 /
> MONTH VALUE TILL_MONTH_VALUE
> ---------- ---------- ----------------
> Jan 1000 1000
> Feb 2000 3000
> Mar 800 3800
> Apr 600 4400
> May 1400 5800
> Jun 730 6530
> Jul 970 7500
>
> 7 rows selected.
>
> Regards
> Michel Cadot
I got fooled by the following statement:
"RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The short form of
this is RANGE UNBOUNDED PRECEDING."
Due to not testing the long form (BETWEEN ... AND ...) my attempts
failed.
I thought the short form is equivalent and produces identical results
as the long form.
Obvious, that's not right.
Thank you for enlighting my way!
Alex Sauer
Received on Thu Sep 28 2006 - 03:53:03 CDT
