게시판유형과 세부유형 그리고 상태값을 갖는 게시판의 통계를 구하려고 합니다.
==================================================================
게시판유형 // 세부유형 // 상태값(a+b+c) // 상태값 (b) // 상태값(c)
------------------------------------------------------------------
A 1 5 1 3
------------------------------------------------------------------
. . . . .
------------------------------------------------------------------
. . . . .
. . . . .
. . . . .
==================================================================
위와 같은 결과를 내려고 아래와 같은 쿼리를 작성했습니다.
================================================================================
SELECT A.BOARDTYPE
, A.DETAILTYPE, COALESCE(COUNT(*), 0) AS STATE
, COALESCE(B.STATE, 0) AS STATE1
, COALESCE(C.STATE, 0) AS STATE2
FROM WEB_BOARD A
LEFT OUTER JOIN (SELECT BOARDTYPE, DETAILTYPE, COUNT(*) STATE
FROM WEB_BOARD
WHERE STATE = 2
AND BOARDTYPE IS NOT NULL
AND DETAILTYPE IS NOT NULL
AND REGION IS NOT NULL
AND COMPANYNAME IS NOT NULL
AND BOARDID = NID
GROUP BY BOARDTYPE, DETAILTYPE) B
ON A.BOARDTYPE = B.BOARDTYPE AND A.DETAILTYPE = B.DETAILTYPE
LEFT OUTER JOIN (SELECT BOARDTYPE, DETAILTYPE, COUNT(*) STATE
FROM WEB_BOARD
WHERE STATE = 3
AND BOARDTYPE IS NOT NULL
AND DETAILTYPE IS NOT NULL
AND REGION IS NOT NULL
AND COMPANYNAME IS NOT NULL
AND BOARDID = NID
GROUP BY BOARDTYPE, DETAILTYPE) C
ON A.BOARDTYPE = C.BOARDTYPE AND A.DETAILTYPE = C.DETAILTYPE
WHERE A.STATE Is Not NULL
And A.REGION Is Not NULL
And A.COMPANYNAME Is Not NULL
AND A.BOARDTYPE IS NOT NULL
AND A.DETAILTYPE IS NOT NULL
AND A.BOARDID = A.NID
GROUP BY A.BOARDTYPE, A.DETAILTYPE, B.STATE, C.STATE
ORDER BY A.BOARDTYPE, A.DETAILTYPE
================================================================================
이 쿼리로 결과값이 나오기는 하는데.
문제는 없는 유형에대해서도 0 값을 받아야 하는데
어찌해야할지 몰라 계속 해메고 있습니다.
조금만 도와주세요...ㅜㅜ
아래와같은 유형이 있을때,
****************************
게시물유형 A , B , C
세부유형 1 , 2 , 3
****************************
원하는 결과는 이건데.
============================================
A 1 5 3 1
2 0 0 0
3 4 0 3
B 1 0 0 0
2 3 2 0
3 4 2 2
C 1 0 0 0
2 7 1 4
3 4 2 0
============================================
지금은 이렇게 나와요.
============================================
A 1 5 3 1
3 4 0 3
B 2 3 2 0
3 4 2 2
C 2 7 1 4
3 4 2 0
============================================
