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

Matej Kavcic

Delphi Developer
Sun, 12 Oct 2003 17:26:34 GMT

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

 
Ping Ka

Delphi Developer
Mon, 13 Oct 2003 07:51:20 GMT

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

Erez

Delphi Developer
Fri, 17 Oct 2003 00:58:25 GMT

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

1. Select Sum(x)/Sum(y)

2. help: Select SUM (amount)

3. Problem with Query (select sum(*) ...)

4. Elementary database enquiry : Select sum-waht then

5. Select Sum generate multi connections

6. ADOQuery and SELECT SUM

7. SELECT SUM

8. computed by (select sum(field))

9. SQL query: SELECT SUM(time) ...

10. QUERY: SELECT SUM