Monday, May 01, 2006

One row goes to two groups

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: