합계 쿼리 질문 0 2 175

by 다링 [SQL Query] 합계 [2020.07.31 14:28:59]


20200731 5
20200730 3
20200629 4
20200628 2
20200501 3
총합 3 17

통계쿼리를 뽑고있는중인데 문의가있어 글을 남깁니다.

위 표와같이 데이터 하단에 총합이 나왔으면 합니다.

3이나온이유는 가,나,다 이렇게 3가지종류라 3이 나오고,

17은 숫자들의 합계입니다.

 

고수님들의 답변부탁드립니다.

 

감사합니다.

 

by JYK94 [2020.07.31 14:53:30]
WITH T
	 AS (SELECT '20200731' AS DT, '가' AS B, 5 AS C
		   FROM DUAL
		 UNION ALL
		 SELECT '20200730' AS DT, '나' AS B, 3 AS C
		   FROM DUAL
		 UNION ALL
		 SELECT '20200730' AS DT, '가' AS B, 4 AS C
		   FROM DUAL
		 UNION ALL
		 SELECT '20200628' AS DT, '나' AS B, 2 AS C
		   FROM DUAL
		 UNION ALL
		 SELECT '20200501' AS DT, '다' AS B, 3 AS C
		   FROM DUAL)
  SELECT DECODE ( GROUPING ( DT ), 1, '합계', DT ) AS DT
		,DECODE ( GROUPING ( B )
				 ,1,	( SELECT COUNT ( * )
							FROM (	SELECT B
									  FROM T
								  GROUP BY B) )
					 || ''
				 ,B
				 )
			 AS B
		,SUM ( C ) AS C
	FROM T
GROUP BY ROLLUP ( ( DT, B ) )

 -- GROUPING, ROLL UP 참고 : http://www.gurubee.net/lecture/2679

by 마농 [2020.07.31 15:22:15]
WITH t AS
(
SELECT '20200731' dt, '가' cd, 5 v FROM dual
UNION ALL SELECT '20200730', '나', 3 FROM dual
UNION ALL SELECT '20200629', '가', 4 FROM dual
UNION ALL SELECT '20200628', '나', 2 FROM dual
UNION ALL SELECT '20200501', '다', 3 FROM dual
)
SELECT NVL(dt, '총합') dt
     , NVL(cd, COUNT(DISTINCT cd)) cd
     , SUM(v) v
  FROM t a
 GROUP BY ROLLUP((dt, cd))
 ORDER BY a.dt DESC NULLS LAST
;

 

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