select to_char(seq) as seq , case max(seq) when 0 then sum(nvl(num1, 0)) else trunc(avg(nvl(num1, 0)),1) end as num1 , case max(seq) when 0 then sum(nvl(num2, 0)) else trunc(avg(nvl(num2, 0)),1) end as num2 , case max(seq) when 0 then sum(nvl(num3, 0)) else trunc(avg(nvl(num3, 0)),1) end as num3 from edu_condtest group by seq union all select 'total' , case max(seq) when 0 then sum(nvl(num1, 0)) else trunc(avg(nvl(num1, 0)),1) end as num1 , case max(seq) when 0 then sum(nvl(num2, 0)) else trunc(avg(nvl(num2, 0)),1) end as num2 , case max(seq) when 0 then sum(nvl(num3, 0)) else trunc(avg(nvl(num3, 0)),1) end as num3 from edu_condtest union all select 'avg' , case max(seq) when 0 then sum(nvl(num1, 0)) else trunc(avg(nvl(num1, 0)),1) end as num1 , case max(seq) when 0 then sum(nvl(num2, 0)) else trunc(avg(nvl(num2, 0)),1) end as num2 , case max(seq) when 0 then sum(nvl(num3, 0)) else trunc(avg(nvl(num3, 0)),1) end as num3 from edu_condtest
select to_char(seq) as seq , case max(seq) when 0 then sum(nvl(num1, 0)) else trunc(avg(nvl(num1, 0)),1) end as num1 , case max(seq) when 0 then sum(nvl(num2, 0)) else trunc(avg(nvl(num2, 0)),1) end as num2 , case max(seq) when 0 then sum(nvl(num3, 0)) else trunc(avg(nvl(num3, 0)),1) end as num3 from edu_condtest group by seq union all select 'total' , sum(nvl(num1, 0)) as num1 , sum(nvl(num2, 0)) as num2 , sum(nvl(num3, 0)) as num3 from edu_condtest union all select 'avg' , case max(seq) when 0 then sum(nvl(num1, 0)) else trunc(avg(nvl(num1, 0)),1) end as num1 , case max(seq) when 0 then sum(nvl(num2, 0)) else trunc(avg(nvl(num2, 0)),1) end as num2 , case max(seq) when 0 then sum(nvl(num3, 0)) else trunc(avg(nvl(num3, 0)),1) end as num3 from edu_condtest
-- Case 나 Group By 가 필요 없어 보입니다. -- NVL 은 컬럼에 직접 하지 말고 집계후 한번만 합니다. SELECT TO_CHAR(seq) AS seq , NVL(num1, 0) num1 , NVL(num2, 0) num2 , NVL(num3, 0) num3 FROM edu_condtest UNION ALL SELECT 'total' , NVL(SUM(num1), 0) num1 , NVL(SUM(num2), 0) num2 , NVL(SUM(num3), 0) num3 FROM edu_condtest UNION ALL SELECT 'avg' , NVL(TRUNC(AVG(num1), 1), 0) num1 , NVL(TRUNC(AVG(num2), 1), 0) num2 , NVL(TRUNC(AVG(num3), 1), 0) num3 FROM edu_condtest ;
글쎄요...
왜? sum 을 사용해야 한다고 생각하는지 모르겠네요.
NULLIF(v, v1) : v가 v1과 같으면 널, 그렇지 않으면 v 그대로
NullIf 사용 안하고 같은 기능 구현하기
CASE WHEN v = v1 THEN null ELSE v END
DECODE(v, v1, null, v)
실전에 적용하기
(NVL(num1, 0) + NVL(num2, 0) + NVL(num3, 0))
/ DECODE
( NVL2(num1, 1, 0) + NVL2(num2, 1, 0) + NVL2(num3, 1, 0), 0, Null
, NVL2(num1, 1, 0) + NVL2(num2, 1, 0) + NVL2(num3, 1, 0)
)
다른 방법으로 접근하기
CASE
WHEN num1 IS NULL AND num2 IS NULL AND num3 IS NULL
THEN Null
ELSE (NVL(num1, 0) + NVL(num2, 0) + NVL(num3, 0))
/ (NVL2(num1, 1, 0) + NVL2(num2, 1, 0) + NVL2(num3, 1, 0))
END
방법은 무궁무진합니다.
정해진 답이 있다 해도...
비효율이 있어서 그것을 바로 잡아주는것은 몰라도
비효율이 없는데도 정해진 답과 다르다는 이유로 트집을 잡는다면 쫌....