데이터베이스 질문과 답변
PARITION BY 할때 COUNT
0
2
1,071
by 농부지기 [2019.10.01 11:35:04]
아래 결과를 보면 아래 같은데요.
L M S GRP_CNT, ALL_CNT
52 106 2 2 2
52 106 2 3 2
52 106 2 1 1
전 아래처럼 나오게 하고 싶습니다.
L M S GRP_CNT, ALL_CNT
52 106 2 2 5
52 106 2 3 5
52 106 2 1 1
확인해보니 PARTITION BY는 GROUP BY 후에 진행이 되네요.
전 PARTIONTION BY가 GROUP 전에 될줄 알았는데..ㅠㅠ
어떻게 하면 가능할까요?
부탁 합니다.
WITH V_PART AS
( SELECT 52 AS L, 106 AS M, 2 AS S, 1 AS ANS FROM DUAL UNION ALL
SELECT 52 AS L, 106 AS M, 2 AS S, 1 AS ANS FROM DUAL UNION ALL
SELECT 52 AS L, 106 AS M, 2 AS S, 3 AS ANS FROM DUAL UNION ALL
SELECT 52 AS L, 106 AS M, 2 AS S, 3 AS ANS FROM DUAL UNION ALL
SELECT 52 AS L, 106 AS M, 2 AS S, 3 AS ANS FROM DUAL UNION ALL
SELECT 53 AS L, 206 AS M, 3 AS S, 3 AS ANS FROM DUAL
)
SELECT L, M, S
, COUNT(*) AS GRP_CNT
, COUNT(*) OVER (PARTITION BY L, M, S) AS ALL_CNS
FROM V_PART
GROUP BY L,M,S, ANS;
by jkson
[2019.10.01 13:08:24]
SELECT L, M, S
, COUNT(*) AS GRP_CNT
, SUM(COUNT(*)) OVER (PARTITION BY L, M, S) AS ALL_CNS
FROM V_PART
GROUP BY L,M,S, ANS;
by 농부지기
[2019.10.01 13:44:08]
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.