SQL> select * from tst;
C1 C2 C3
---------- ---------- ----------
1 150
1 1 80
1 2 90
1 1 130
1 3 120
Make sum on C2 based on group (c1, c2), the special requirement is
within the group of (1, null), it should include 10% of the sum of
other groups which c2 is not null. To achive this, one row will go
to 2 groups. one for itself and one maps to (c1, null).
The query will be
select
C1,
(CASE WHEN TYPE = 1 THEN C2
WHEN TYPE = 2 THEN NULL
END) C2,
SUM(CASE WHEN TYPE = 1 THEN C3
WHEN TYPE = 2 AND C2 IS NOT NULL THEN C3*0.1
ELSE NULL
END) C3_SUM
from
(
select c1, c2, sum(c3) c3
from tst
group by c1, c2
), (select 1 type from dual union all select 2 type from dual
)
group by
C1,
(CASE WHEN TYPE = 1 THEN C2
WHEN TYPE = 2 THEN NULL
END)
and result is
C1 C2 C3_SUM
---------- ---------- ----------
1 192
1 1 210
1 2 90
1 3 120
No comments:
Post a Comment