데이터베이스 질문과 답변
group by 대상에 item1, item2, item3, item4, item5가 있는데
어떤 회원은 item1, item4 밖에서 없어서
group by를 하면 두 줄밖에 안 나오는데요,
item2,3,5도 각각 0으로 표시를 하고싶은데.. 이럴때는 어떻게 해줘야 하는지 잘 모르겠습니다ㅠ
조언부탁드립니다~
select type, count where id = '12'
group by type
결과:
item1 2
item4 5
원하는 결과:
item1 2
item2 0
item3 0
item4 5
item5 0
by 개발고수가되기를
[2019.08.27 14:30:26]
nvl(컬럼, 0) 해보셔요
by 우리집아찌
[2019.08.27 14:44:12]
-- TYPE이 공통코드로 되어있다면
WITH T AS (
SELECT 12 ID , 'item1' TYPE FROM DUAL UNION ALL
SELECT 12 ID , 'item1' TYPE FROM DUAL UNION ALL
SELECT 12 ID , 'item4' TYPE FROM DUAL UNION ALL
SELECT 12 ID , 'item4' TYPE FROM DUAL UNION ALL
SELECT 12 ID , 'item4' TYPE FROM DUAL UNION ALL
SELECT 12 ID , 'item4' TYPE FROM DUAL UNION ALL
SELECT 12 ID , 'item4' TYPE FROM DUAL
) , CODE AS (
SELECT 'item1' TYPE FROM DUAL UNION ALL
SELECT 'item2' TYPE FROM DUAL UNION ALL
SELECT 'item3' TYPE FROM DUAL UNION ALL
SELECT 'item4' TYPE FROM DUAL UNION ALL
SELECT 'item5' TYPE FROM DUAL
)
SELECT B.TYPE , NVL(cnt,0) cnt
FROM (
SELECT type
,COUNT(*) cnt
FROM T
WHERE ID = '12'
GROUP BY type
) A
, CODE B
WHERE A.TYPE(+) = B.TYPE
ORDER BY B.TYPE
by 초초
[2019.08.27 15:18:11]
감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.