Help on query

[zzz]

could someone help me to formulate this query? i think this would require use of analytical functions. but i am getting an error while using group by with analytical function...

the source table data and the output required is:

for f5 sum of first value for f1+f2+f3 combination should be taken.
for f6 is a simple sum.
for f7 sum of last value for f1+f2+f3 combination should be taken.
for f8, if f7/f5

source table data
-----------------

f1 f2 f3 f4 f5 f6 f7
1 1 1 28-MAR-2003 200 50 150
1 1 1 29-MAR-2003 200 10 120
1 1 2 31-MAR-2003 250 20 110
1 2 1 28-MAR-2003 300 25 200
1 2 2 29-MAR-2003 200 45 190
1 2 2 31-MAR-2003 200 20 180

output required:

f1 f2 f4 f5 f6 f7 f8 = f7/f5
1 1 MAR-2003 450 80 230 230/450 = 0.51
1 2 MAR-2003 500 90 380 380/500 = 0.76

zzz

[apar_6]

Hi,
I have not used analytical functions, but just tried the query below :

select res.f1, res.f2, res.f4, sum(res.f5), sum(res.f6), sum(res.f7), sum(res.f7)/sum(res.f5)
from (
select
f1, f2, to_char(f4, 'MON-YYYY') f4,
sum(distinct f5) f5,
sum(f6) f6,
min(f7) f7
from chk_sum c
group by f1, f2, f3, to_char(f4, 'MON-YYYY')
) res
group by res.f1, res.f2, res.f4

Does this help ?

Reg,
Aparna S.

[chrisrlong]

Okay, so what happened to this question the last time it was asked??

I can't find it. Was it deleted?

- Chris