[MS-SQL] 소계 출력시 총계 대비 비율(%) 구하기 (RATIO_TO_REPORT)

ORACLE의 RATIO_TO_REPORT를 MS-SQL에서 구현해보자.

오라클의 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

혹시나 ROLLUP을 사용하면서 구할경우에는 ×2를 해주면서 구하면 된다.

    SELECT ISNULL(CNT_GROUP, 'TOTAL')                   AS [그룹]
         , SUM(CNT)                                     AS [소계]
         , SUM(CNT) * 100 * 2 / 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 WITH ROLLUP

관련글

날다의 운영환경
본체	MacBook Pro
O S	Windows7 Ultimate K
Application	Microsoft SQL Server Management Studio 2017 V17.2
MS-SQL	Micorsoft SQL Server 2016(SP1-CU5)