SQL Server - Grouping Sets 함수 Grouping Sets in SQL Server

그룹핑 셋 Grouping Sets  함수

 

2008년에 소개된 이 기능은 다음과 같은 예시를 간단하게 해결하기 위한 것이다.

Select Country, Gender, Sum(Salary) as TotalSalary
From Employees  
Group By Country, Gender

UNION ALL

Select Country, NULL, Sum(Salary) as TotalSalary
From Employees  
Group By Country

UNION ALL

Select NULL, Gender, Sum(Salary) as TotalSalary
From Employees  
Group By Gender

UNION ALL

Select NULL, NULL, Sum(Salary) as TotalSalary
From Employees 

 

Select Country, Gender, Sum(Salary) TotalSalary
From Employees 
Group BY 
      GROUPING SETS 
      ( 
            (Country, Gender), -- Sum of Salary by Country and Gender
            (Country),   -- Sum of Salary by Country
            (Gender) ,   -- Sum of Salary by Gender
            ()     -- Grand Total
      )
Order By Grouping(Country), Grouping(Gender), Gender
​