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

Mir's 운영환경

본체	MacBook Pro
O S	Windows7 Ultimate K (Service Pack 1)
APP	Microsoft SQL Server Management Studio  2017 V17.2
MS-SQL	Micorsoft SQL Server 2016(SP1-CU5)

소계 출력시 총걔 대비 비율 구하기

---

※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
    

보고 있는 글과 같이 보면 좋은 글들..