SELECT
cpy
,year
,exp(sum(ln(amount)))
FROM
mytable
GROUP BY
cpy
,year
SQL> select * from tbl1;
CPY YEAR AMOUNT
-------------------- ---------- ----------
A 2008 10
A 2008 20
A 2009 40
B 2008 1
B 2008 2
B 2008 3
6 rows selected.
SQL> with data as (
2 select cpy, year, amount,
3 row_number() over (partition by cpy, year order by(1)) rn,
4 count(1) over (partition by cpy, year order by(1)) cn
5 from tbl1
6 ),
7 x(cpy, year, amount, rn, cn) as (
8 select cpy, year, amount, rn, cn
9 from data
10 where rn = 1
11 union all
12 select data.cpy, data.year, data.amount * x.amount, data.rn, data.cn
13 from data, x
14 where data.cpy = x.cpy
15 and data.year=x.year
16 and data.rn = x.rn+1)
17 select cpy, year, amount
18 from x
19 where rn = cn
20 order by cpy, year;
CPY YEAR AMOUNT
-------------------- ---------- ----------
A 2008 200
A 2009 40
B 2008 6
SQL>
Title | # Comments | Views | Activity |
---|---|---|---|
Delphi selector screen | 2 | 57 | |
Copy Oracle database from 11g (11.2.0.1.0) to 12c (12.1.0.2.0) | 15 | 104 | |
Using field values to generate columns - oracle query | 6 | 46 | |
How to quickly enable constraint with ENABLE VALIDATE for a big table in 12c | 13 | 32 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
7 Experts available now in Live!