MSSQL 소계, 총합 구하기

프로젝트를 하다보면 아주 빈번하게 하게되는 작업이 바로 통계 장표를 만드는 일일덴데요. 통계 화면에서는 제품별, 날짜별 등의 특정 칼럼에 대한 합을 많이 표현하지요.

하여 오늘은 MSSQL 소계, 총합 구하기에 대해서 알아보겠습니다.
(컨트롤에 Trigger 등의 이벤트를 생성해서 click이벤트를 걸우준 경우는 ie9 이하에서는 에러를 발생 합니다.)




아래의 쿼리를 참고하시면 되겠습니다.

-- 테이블 변수를 생성합니다.
DECLARE @T_STATIC TABLE
(
    TREETITLE        VARCHAR(20),
    TERMNAME        VARCHAR(20),
    TERMOFFTYPENAME    VARCHAR(20),
    TERMSTARTDATE    SMALLDATETIME,
    TERMENDDATE        SMALLDATETIME,
    COMPANYNAME    VARCHAR(20),
    USERNAME        VARCHAR(20)
)

-- 테스트 할 데이터를 등록 합니다.
INSERT INTO @Table VALUES ('treetitle1','termname2','termofftypename','2019-02-21','2007-02-23','companyname1','username1')
INSERT INTO @Table VALUES ('treetitle1','termname1','termofftypename','2019-02-22','2007-02-23','companyname1','username2')
INSERT INTO @Table VALUES ('treetitle1','termname3','termofftypename','2019-02-24','2007-02-26','companyname2','username3')
INSERT INTO @Table VALUES ('treetitle1','termname4','termofftypename','2019-02-27','2007-02-28','companyname3','username4')
INSERT INTO @Table VALUES ('treetitle1','termname5','termofftypename','2019-02-26','2007-02-27','companyname4','username5')
INSERT INTO @Table VALUES ('treetitle1','termname6','termofftypename','2019-02-22','2007-02-23','companyname5','username6')
INSERT INTO @Table VALUES ('treetitle1','termname6','termofftypename','2019-02-22','2007-02-23','companyname6','username4')

INSERT INTO @Table VALUES ('treetitle2','termname1','termofftypename','2019-02-22','2007-02-23','companyname7','username7')
INSERT INTO @Table VALUES ('treetitle2','termname2','termofftypename','2019-02-21','2007-02-23','companyname1','username8')
INSERT INTO @Table VALUES ('treetitle2','termname3','termofftypename','2019-02-24','2007-02-26','companyname2','username9')
INSERT INTO @Table VALUES ('treetitle2','termname4','termofftypename','2019-02-27','2007-02-28','companyname18','username10')
INSERT INTO @Table VALUES ('treetitle2','termname5','termofftypename','2019-02-26','2007-02-27','companyname9','username10')
INSERT INTO @Table VALUES ('treetitle2','termname6','termofftypename','2019-02-22','2007-02-23','companyname10','username11')
INSERT INTO @Table VALUES ('treetitle2','termname7','termofftypename','2019-02-22','2007-02-23','companyname11','username8')

INSERT INTO @Table VALUES ('treetitle3','termname1','termofftypename','2019-02-22','2007-02-23','companyname12','username12')
INSERT INTO @Table VALUES ('treetitle3','termname1','termofftypename','2019-02-21','2007-02-23','companyname13','username10')
INSERT INTO @Table VALUES ('treetitle3','termname1','termofftypename','2019-02-24','2007-02-26','companyname14','username9')
INSERT INTO @Table VALUES ('treetitle3','termname1','termofftypename','2019-02-27','2007-02-28','companyname15','username13')
INSERT INTO @Table VALUES ('treetitle3','termname1','termofftypename','2019-02-26','2007-02-27','companyname16','username10')
INSERT INTO @Table VALUES ('treetitle3','termname1','termofftypename','2019-02-22','2007-02-23','companyname17','username14')
INSERT INTO @Table VALUES ('treetitle3','termname1','termofftypename','2019-02-22','2007-02-23','companyname15','username14')




-- 테스트 데이터를 한번 확인해 줍니다.
SELECT * FROM @Table


SELECT
            TREETITLE,
            T_SORT = TREETITLE, -- 소트를 하기위한 컬럼생성
            T_CKS = 'A', -- 소트구분을 위한 구분자
            TERMNAME,
            TERMSTARTDATE,
            TERMENDDATE,
            COMPANYNAME,
            COUNT(USERNAME) AS CNT
FROM @Table
GROUP BY TREETITLE, TERMNAME, TERMSTARTDATE, TERMENDDATE, COMPANYNAME

UNION ALL

SELECT '소계',
            T_SORT = TREETITLE,
            T_CKS = 'Z',
            '-',
            NULL,
            NULL,
            '-',
            COUNT(USERNAME)
FROM @Table
GROUP BY TREETITLE

UNION ALL
SELECT
            '총합',
            T_SORT = 'Z',
            T_CKS = 'Z',
            '-',
            NULL,
            NULL,
            '-',
            COUNT(USERNAME)
FROM @Table
ORDER BY T_SORT, T_CKS

Rollup을 사용하여 소계, 합계를 구할 수 있지만 여기서는 Union 과 정렬을 사용하여 구하는 방식을 사용하겠습니다.




Rollup을 사용하는 방법도 같이 알아보겠습니다.
아래 쿼리를 참고해 주세요.

SELECT       
    CASE WHEN GROUPING(COMPANYNAME) = 1 then (CASE WHEN  GROUPING(TREETITLE) = 1 THEN '합계' ELSE '소계' END)
    ELSE TREETITLE END as TREETITLE,
    CASE WHEN GROUPING(COMPANYNAME) = 1 then NULL ELSE max(TERMNAME) END as TERMNAME,
    CASE WHEN GROUPING(COMPANYNAME) = 1 then NULL ELSE COMPANYNAME END as COMPANYNAME,
    CASE WHEN GROUPING(COMPANYNAME) = 1 then NULL ELSE max(TERMSTARTDATE) END as TERMSTARTDATE,
    CASE WHEN GROUPING(COMPANYNAME) = 1 then NULL ELSE max(TERMENDDATE) END as TERMENDDATE,
    COUNT(USERNAME) as CNT,
    CASE WHEN GROUPING(COMPANYNAME) = 1 then (CASE WHEN  GROUPING(TREETITLE) = 1 THEN '합계' ELSE left(TREETITLE,1) +'-소계' END)
    ELSE left(TREETITLE,1) END as orderby
FROM @Table
GROUP BY TREETITLE, COMPANYNAME WITH ROLLUP
order by orderby, TERMNAME, COMPANYNAME asc

이상으로 MSSQL 소계, 총합 구하기에 관해서 알아보았습니다.