[DATABASE/SQLSERVER] GROUP BY문 사용시 소계/비율/총계 구하기 (WITH ROLLUP 사용시)

icodebroker SQLSERVER 2024-06-18

■ GROUP BY문 사용시 소계/비율/총계를 구하는 방법을 보여준다.

▶ 예제 코드 (SQL)


SELECT
    ISNULL(SalesGroup, 'TOTAL')                               AS [판매 그룹]
   ,SUM(SalesAmount)                                          AS [소계]
   ,SUM(SalesAmount) * 100 * 2 / SUM(SUM(SalesAmount)) OVER() AS [비율]
   ,SUM(SUM(SalesAmount)) OVER()                              AS [총계]
FROM
(
    SELECT 10 AS SalesAmount, '영업1팀' AS SalesGroup UNION ALL
    SELECT 10 AS SalesAmount, '영업1팀' AS SalesGroup UNION ALL
    SELECT 60 AS SalesAmount, '영업2팀' AS SalesGroup UNION ALL
    SELECT 40 AS SalesAmount, '영업2팀' AS SalesGroup UNION ALL
    SELECT 40 AS SalesAmount, '영업3팀' AS SalesGroup UNION ALL
    SELECT 25 AS SalesAmount, '영업3팀' AS SalesGroup UNION ALL
    SELECT 15 AS SalesAmount, '영업3팀' AS SalesGroup
) A
GROUP BY SalesGroup WITH ROLLUP;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

SELECT

ISNULL(SalesGroup, 'TOTAL') AS [판매 그룹]

,SUM(SalesAmount) AS [소계]

,SUM(SalesAmount) * 100 * 2 / SUM(SUM(SalesAmount)) OVER() AS [비율]

,SUM(SUM(SalesAmount)) OVER() AS [총계]

FROM

(

SELECT 10 AS SalesAmount, '영업1팀' AS SalesGroup UNION ALL

SELECT 60 AS SalesAmount, '영업2팀' AS SalesGroup UNION ALL

SELECT 40 AS SalesAmount, '영업2팀' AS SalesGroup UNION ALL

SELECT 40 AS SalesAmount, '영업3팀' AS SalesGroup UNION ALL

SELECT 25 AS SalesAmount, '영업3팀' AS SalesGroup UNION ALL

SELECT 15 AS SalesAmount, '영업3팀' AS SalesGroup

) A

GROUP BY SalesGroup WITH ROLLUP;