1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
|
SQL> with t as (select 'A' as c, 2 as c2 from dual
2 union all
3 select 'A' as c, 5 as c2 from dual
4 union all
5 select 'A' as c, 0 as c2 from dual
6 union all
7 select 'B' as c, 2 as c2 from dual
8 union all
9 select 'B' as c, 6 as c2 from dual
10 union all
11 select 'B' as c, 2 as c2 from dual)
12 select t.c,
13 case when temp_s.s1!=temp_s.s2 then 0
14 else exp(sum(ln(replace(c2,0,1)))) end as produit
15 from t join (select c,sum(c2) as s1, sum(replace(c2,0,1)) as s2 from t group by c) temp_s
16 on t.c=temp_s.c
17 group by t.c, temp_s.s1,temp_s.s2;
C PRODUIT
- ----------
A 0
B 24 |
Partager