Re: Dynamic calculation

<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 Received on Wed Sep 27 2006 - 10:48:23 CDT