[집계] 그룹별 소계, 총계, 비율 구하기

ORACLE에서는 RATIO_TO_REPORT함수를 이용이 가능하지만, 

MS-SQL에서는 불가능하다.

 

따라서 직접 집계를 내보도록 하겠다.

 

SELECT CNT_GROUP									AS  '그룹'
	, SUM(CNT)										AS	'소계'
    , SUM(CNT) * 100 / SUM(SUM(CNT))OVER()			AS  '차지 비율'
    , SUM(SUM(CNT)) OVER()							AS  '총계'
FROM(
	SELECT 10 AS CNT, 'GROUP1' AS CNT_GROUP UNION ALL
    SELECT 10 AS CNT, 'GROUP1' AS CNT_GROUP UNION ALL
    SELECT 60 AS CNT, 'GROUP2' AS CNT_GROUP UNION ALL
    SELECT 40 AS CNT, 'GROUP2' AS CNT_GROUP UNION ALL
    SELECT 40 AS CNT, 'GROUP3' AS CNT_GROUP UNION ALL
    SELECT 25 AS CNT, 'GROUP3' AS CNT_GROUP UNION ALL
    SELECT 15 AS CNT, 'GROUP3' AS CNT_GROUP
    ) A
GROUP BY CNT_GROUP

 

그룹	소계	차지 비율	총계
GROUP1	20	10	200
GROUP2	100	50	200
GROUP3	80	40	200

실행을 하면 위와 같은 결과가 도출된다.