[phil-story] Always Fighting 

SELECT ProductID
      ,Name
      ,ProductNumber
      ,SafetyStockLevel
      ,ReorderPoint
  FROM Production.Product
 WHERE ProductID < 320
 

3..합계 행 표시
SELECT CASE WHEN GROUPING(ProductID) = 1 THEN  N'합계' ELSE ProductID END AS ProductID
      ,CASE WHEN GROUPING(ProductID) = 1 THEN  '' ELSE MAX(Name) END AS Name
      ,CASE WHEN GROUPING(ProductID) = 1 THEN  '' ELSE MAX(ProductNumber) END AS ProductNumber
      ,SUM(SafetyStockLevel) AS SafetyStockLevel
      ,SUM(ReorderPoint) AS ReorderPoint
      ,GROUPING(ProductID) AS GroupingProductID
  FROM Production.Product
 WHERE ProductID < 320
 GROUP BY ProductID WITH ROLLUP

메시지 245, 수준 16, 상태 1, 줄 2
nvarchar 값 '합계'을(를) 데이터 형식 int(으)로 변환하지 못했습니다.

SELECT CASE WHEN GROUPING(ProductID) = 1 THEN  N'합계' ELSE CONVERT(VARCHAR(5),ProductID) END AS ProductID
      ,CASE WHEN GROUPING(ProductID) = 1 THEN  '' ELSE MAX(Name) END AS Name
      ,CASE WHEN GROUPING(ProductID) = 1 THEN  '' ELSE MAX(ProductNumber) END AS ProductNumber
      ,SUM(SafetyStockLevel) AS SafetyStockLevel
      ,SUM(ReorderPoint) AS ReorderPoint
      ,GROUPING(ProductID) AS GroupingProductID
  FROM Production.Product
 WHERE ProductID < 320
 GROUP BY ProductID WITH ROLLUP

4..ProductUnit 칼럼 추가하기

SELECT 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
      ,ProductID
      ,Name
      ,ProductNumber
      ,SafetyStockLevel
      ,ReorderPoint
  FROM Production.Product
 WHERE ProductID < 320

5..ProductUnit와 ProductID로 grouping

SELECT ProductUnit
      ,ProductID
      ,MAX(Name) AS Name
      ,MAX(ProductNumber) 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

6..합계, 소계 행 표시
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

7..소계행을 제외하고 합계행만 표시
SELECT CASE WHEN GROUPING(ProductUnit) = 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 UnitGroup
      ,GROUPING(ProductID) AS IDGroup
  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
 HAVING GROUPING(ProductUnit)+GROUPING(ProductID) IN (0,2)