Board index » delphi » How to intermediate sum on SELECT with ORACLE

How to intermediate sum on SELECT with ORACLE

Hello I have one question

 I have one simple table TEST and it looks like

Id   Name     Price
------------------
1     test1       10
2     test2       20
3     test3       30

Okey now I want to create some simple query in ORACLE to get next result

Name   Price  PriceSK
---------------------
test1       10       10
test2       20       30
test3       30       60

where PriceSK is PriceSK in prior row+Price in this row

 I try with
SELECT Name,Price, PriceSK+Price as PriceSK
FROM test
but it is not working

Could somebody help me and write me Query to do that

lp

Matej

 

Re:How to intermediate sum on SELECT with ORACLE


Quote
Matej Kavcic <matej.a.kav...@kiss.uni-lj.si> wrote in message

news:3ae698ce$1_2@dnews...
Quote
> Hello I have one question

>  I have one simple table TEST and it looks like

> Id   Name     Price
> ------------------
> 1     test1       10
> 2     test2       20
> 3     test3       30

> Okey now I want to create some simple query in ORACLE to get next result

> Name   Price  PriceSK
> ---------------------
> test1       10       10
> test2       20       30
> test3       30       60

> where PriceSK is PriceSK in prior row+Price in this row

>  I try with
> SELECT Name,Price, PriceSK+Price as PriceSK
> FROM test
> but it is not working

> Could somebody help me and write me Query to do that

> lp

> Matej

select t1.name, price, sk_price from
test t1,
(select t2.name, sum(v2.price) sk_price
from test t2,
(select name, price from test) v2
where v2.name <= t2.name
group by t2.name
) v1
where v1.name = t1.name

This should do what your want.  Performance may be bad though.

Ping Kam

Re:How to intermediate sum on SELECT with ORACLE


In Oracle 8.1.6 and above you may use the Analitic functions.

Quote
"Matej Kavcic" <matej.a.kav...@kiss.uni-lj.si> wrote in message

news:3ae698ce$1_2@dnews...
Quote
> Hello I have one question

>  I have one simple table TEST and it looks like

> Id   Name     Price
> ------------------
> 1     test1       10
> 2     test2       20
> 3     test3       30

> Okey now I want to create some simple query in ORACLE to get next result

> Name   Price  PriceSK
> ---------------------
> test1       10       10
> test2       20       30
> test3       30       60

> where PriceSK is PriceSK in prior row+Price in this row

>  I try with
> SELECT Name,Price, PriceSK+Price as PriceSK
> FROM test
> but it is not working

> Could somebody help me and write me Query to do that

> lp

> Matej

Other Threads