CREATE TABLE dbo.GroupingNULLS (
Store nvarchar(19)
,SaleYear nvarchar(4)
,SaleMonth nvarchar (7)
,SalePrice int
,NonePrice int
)
INSERT INTO dbo.GroupingNULLS VALUES
(NULL,NULL,'January',0,0)
,(NULL,'2002',NULL,0,0)
,(NULL,NULL,NULL,0,0)
,('Active Cycling',NULL ,'January',0,0)
,('Active Cycling','2002',NULL,4,0)
,('Active Cycling',NULL ,NULL,0,2)
,('Active Cycling',NULL,'January',1,0)
,('Active Cycling','2003','Febuary',0,0)
,('Active Cycling','2003',NULL,0,4)
,('Mountain Bike Store','2002','January',0,0)
,('Mountain Bike Store','2002',NULL,4,0)
,('Mountain Bike Store',NULL,NULL,0,0)
,('Mountain Bike Store','2003','January',0,0)
,('Mountain Bike Store','2003','Febuary',0,3)
,('Mountain Bike Store','2003','March',10,0);
SELECT ISNULL(Store,
CASE WHEN GROUPING(Store) = 0 THEN 'UNKNOWN' ELSE 'ALL' END)
AS Store
,ISNULL(CAST(SaleYear AS nvarchar(7)),
CASE WHEN GROUPING(SaleYear)= 0 THEN 'UNKNOWN' ELSE 'ALL' END)
AS SalesYear
,ISNULL(SaleMonth,
CASE WHEN GROUPING(SaleMonth) = 0 THEN 'UNKNOWN' ELSE 'ALL'END)
AS SalesMonth
,sum(SalePrice) AS DcCount
,SUM(nonePrice) as Count
FROM dbo.GroupingNULLS
GROUP BY ROLLUP(Store, SaleYear, SaleMonth);
부분합 Rollup 예제 참조.
