쿼리로 합계,소계 만들기

SELECT CASE WHEN GROUPING(ProductUnit) = 1 THEN N'합계'
                      WHEN GROUPING(ProductUnit)+GROUPING(ProductID) = 1 THEN N'소계'
                      ELSE ProductUnit END AS ProductUnit
      ,CASE WHEN GROUPING(ProductID) = 0 THEN CONVERT(VARCHAR(5),ProductID)
                 ELSE '' END AS ProductID
      ,CASE WHEN GROUPING(ProductID) = 0 THEN MAX(Name)
                 ELSE '' END AS Name
      ,CASE WHEN GROUPING(ProductID) = 0 THEN MAX(ProductNumber)
                 ELSE '' END AS ProductNumber
      ,SUM(SafetyStockLevel) AS SafetyStockLevel
      ,SUM(ReorderPoint) AS ReorderPoint
      ,GROUPING(ProductUnit) AS GroupingUnit
      ,GROUPING(ProductID) AS GroupingID
  FROM ( SELECT ProductID
               ,Name
               ,CASE WHEN LEFT(ProductNumber,1) = 'A' THEN 'A'
                         WHEN LEFT(ProductNumber,1) = 'B' THEN 'B'
                         WHEN LEFT(ProductNumber,1) = 'C' THEN 'C'
                END AS ProductUnit
               ,ProductNumber
               ,SafetyStockLevel
               ,ReorderPoint
           FROM Production.Product
          WHERE ProductID < 320  ) AS A
 GROUP BY ProductUnit, ProductID WITH ROLLUP

[출처] rollup을 이용한 합계행 쿼리..|작성자 선경