PARITION BY 할때 COUNT 0 2 983

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]

감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입