Re: Dynamic calculation

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 28 Sep 2006 17:49:56 +0200
Message-ID: <451bef24$0$25532$626a54ce@news.free.fr>

"Alex" <dead.man.walking_at_gmx.de> a écrit dans le message de news: 1159426319.105263.113830_at_h48g2000cwc.googlegroups.com... 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

---

All these forms are equivalent and return the same values:

SQL> select month, value,

  2         sum(value) over (order by to_date(month,'Mon')
  3         range between unbounded preceding and current row) till_month_value

  4 from t
  5 /
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.

SQL> select month, value,

  2         sum(value) over (order by to_date(month,'Mon')
  3         range unbounded preceding) till_month_value

  4 from t
  5 /
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.

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 Thu Sep 28 2006 - 10:49:56 CDT