데이터베이스 질문과 답변
by 창조의날개 [2015.02.16 18:32:27]
WITH TT AS(
SELECT 1 AAA, 2 BBB, '20150101' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150101' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150105' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150105' DDD FROM DUAL
)
SELECT SUM(AAA), SUM(BBB)
FROM TT
WHERE DDD = '20151010'
GROUP BY DDD
;
위와 같이 SUM을 구하는데 로우가 하나도 검색되지 않는 경우라도
0을 찍어 주고 싶어서 아래와 같이 해보았습니다.
WITH TT AS(
SELECT 1 AAA, 2 BBB, '20150101' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150101' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150105' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150105' DDD FROM DUAL
)
SELECT SUM(AAA) AAA, SUM(BBB) BBB
FROM (
SELECT SUM(AAA) AAA, SUM(BBB) BBB
FROM TT
WHERE DDD = '20151010'
GROUP BY DDD
UNION ALL
SELECT 0,0 FROM DUAL
)
;
너무 막짠 쿼리 같아서..
뭔가 좀더 깔끔하게 만들수 있지 않을가 싶어서 질문합니다.
고수님들의 고견을 듣고 싶습니다.
감사합니다.
WITH TT AS(
SELECT 1 AAA, 2 BBB, '20150101' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150101' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150105' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150105' DDD FROM DUAL
)
SELECT Nvl(SUM(AAA),0) AAA, Nvl(SUM(BBB),0) BBB
FROM TT
RIGHT OUTER JOIN dual
On DDD = '20151010'
GROUP BY DDD;
고견 까지는 아니구요 음..;;
WITH TT AS(
SELECT 1 AAA, 2 BBB, '20150101' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150101' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150105' DDD FROM DUAL UNION ALL
SELECT 1 AAA, 2 BBB, '20150105' DDD FROM DUAL
)
SELECT SUM(AAA), SUM(BBB)
FROM TT
WHERE DDD = '20151010'
-- GROUP BY DDD
;
답변 감사합니다.
도움이 많이 됬습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.