쿼리를 짜던중에 진행이 안되서 도움을 요청드립니다.
GP_NO | GP_PROJECT_CD | NO | PROJECT_CD | PROJECT_NM |
0 | 0 | A001 | NP0001 | 수행1 |
0 | 0 | A001 | NP0002 | 수행2 |
0 | 1 | 과제건수 | 2 | |
0 | 0 | A002 | NP0003 | 수행3 |
0 | 0 | A004 | NP0004 | 수행4 |
0 | 1 | 2 | ||
1 | 1 | 합계 | 4 |
이렇게 조회 되는것을
GP_NO | GP_PROJECT_CD | NO | PROJECT_CD | PROJECT_NM |
0 | 0 | A001 | NP0001 | 수행1 |
0 | 0 | A001 | NP0002 | 수행2 |
0 | 1 | 과제건수 | 2 | |
0 | 0 | A001 | NP0003 | 수행3 |
0 | 0 | A001 | NP0004 | 수행4 |
0 | 1 | 과제건수 | 2 | |
1 | 1 | 합계 | 4 | |
1 | 1 | 형태별합계 | 3 |
맨하위의 ROW 넣기 위해서 기준이 딱히 SHAPE 건수 세는것 밖에 없는데 가능한가요??
이렇게 형태건수를 한 ROW 맨 아래쪽 A001 사람의 아래쪽에 넣고 싶습니다.
예시쿼리는 아래와 같습니다.
/* Formatted on 2018/11/12 오후 1:43:45 (QP5 v5.256.13226.35510) */
WITH T
AS (SELECT 'A001' AS NO
,'NP0001' AS PROJECT_CD
,'Q0001' AS SHAPE
,1 AS BUSI
,'수행1' AS PROJECT_NM
FROM DUAL
UNION ALL
SELECT 'A001' AS NO
,'NP0002' AS PROJECT_CD
,'Q0001' AS SHAPE
,NULL AS BUSI
,'수행2' AS PROJECT_NM
FROM DUAL
UNION ALL
SELECT 'A001' AS NO
,'NP0003' AS PROJECT_CD
,'' AS SHAPE
,1 AS BUSI
,'수행3' AS PROJECT_NM
FROM DUAL
UNION ALL
SELECT 'A001' AS NO
,'NP0004' AS PROJECT_CD
,'Q0001' AS SHAPE
,NULL AS BUSI
,'수행4' AS PROJECT_NM
FROM DUAL)
SELECT GROUPING (NO) AS GP_NO
,GROUPING (PROJECT_CD) AS PROJECT_CD
,GROUPING (BUSI) AS BUSI
,CASE
WHEN GROUPING (NO) = 0
AND GROUPING (PROJECT_CD) = 1
AND GROUPING (BUSI) = 0
THEN
'과제건수'
WHEN GROUPING (NO) = 0
AND GROUPING (PROJECT_CD) = 1
AND GROUPING (BUSI) = 1
THEN
'합계'
WHEN GROUPING (NO) = 1
AND GROUPING (PROJECT_CD) = 1
AND GROUPING (BUSI) = 1
THEN
'형태별합계'
ELSE
NO
END
NO
,PROJECT_CD
,CASE
WHEN GROUPING (NO) = 0
AND GROUPING (BUSI) = 0
AND GROUPING (PROJECT_CD) = 1
THEN
TO_CHAR (COUNT (*))
WHEN GROUPING (NO) = 0
AND GROUPING (BUSI) = 1
AND GROUPING (PROJECT_CD) = 1
THEN
TO_CHAR (COUNT (*))
WHEN GROUPING (NO) = 1
AND GROUPING (BUSI) = 1
AND GROUPING (PROJECT_CD) = 1
THEN
TO_CHAR (COUNT (*))
ELSE
MAX (PROJECT_NM)
END
AS NM
,BUSI
FROM T
GROUP BY ROLLUP (NO, BUSI, PROJECT_CD)
형태 SHAPE
도움 부탁 드려요 ㅜㅜ
마지막 로우에 합계를 넣는데 굳이 한방쿼리를 고집할 필요가 있을까요?
그냥 union으로 마지막 합계로우만 따로 추가하면 편하게 했을텐데요
원하는 표의 결과값은 아래 count절만 바꾸면 나오긴 하네요
,CASE
WHEN GROUPING (NO) = 0
AND GROUPING (BUSI) = 0
AND GROUPING (PROJECT_CD) = 1
THEN
TO_CHAR (COUNT (*))
WHEN GROUPING (NO) = 0
AND GROUPING (BUSI) = 1
AND GROUPING (PROJECT_CD) = 1
THEN
TO_CHAR (COUNT (*))
WHEN GROUPING (NO) = 1
AND GROUPING (BUSI) = 1
AND GROUPING (PROJECT_CD) = 1
THEN
TO_CHAR (COUNT (*)) --> TO_CHAR (COUNT (SHAPE))
ELSE
MAX (PROJECT_NM)
END
AS NM
WITH T AS ( SELECT 'A001' no, 'NP0001' project_cd, 'Q0001' shape, 1 busi, '수행1' project_nm FROM dual UNION ALL SELECT 'A001', 'NP0002', 'Q0001', null, '수행2' FROM dual UNION ALL SELECT 'A001', 'NP0003', '' , 1, '수행3' FROM dual UNION ALL SELECT 'A001', 'NP0004', 'Q0001', null, '수행4' FROM dual ) SELECT DECODE(GROUPING_ID(no, busi, project_cd), 0, no , 1, '과제건수' , 3, '합계' , 7, '형태별합계' ) no , busi , project_cd , DECODE(GROUPING_ID(no, busi, project_cd), 0, project_nm , 7, COUNT(shape) , COUNT(*) ) project_nm FROM t a GROUP BY ROLLUP(no, busi, (project_cd, project_nm)) ORDER BY a.no, a.busi, a.project_cd ;