ROLLUP、CUBE、および GROUPING SETS を指定した GROUP BY の使用
ROLLUP 演算子、CUBE 演算子、および GROUPING SETS 演算子は GROUP BY 句の拡張機能です。ROLLUP、CUBE、または GROUPING SETS 演算子では、UNION ALL を使用して複数のグループ化クエリを結合した場合と同じ結果セットを取得できますが、通常は、いずれかの GROUP BY 演算子を使用した方が効率的です。
GROUPING SETS 演算子では、単一の GROUP BY、ROLLUP、または CUBE 演算子を使用した場合と同じ結果セットを生成できます。完全な ROLLUP 演算子または CUBE 演算子で生成されるすべてのグループを必要としない場合は、GROUPING SETS を使用して必要なグループのみを指定できます。GROUPING SETS のリストには同じグループを複数指定できます。GROUPING SETS を ROLLUP および CUBE と共に使用すると、同じグループが複数生成される場合があります。重複したグループは、UNION ALL を使用した場合と同様に、保持されます。
注意 |
---|
CUBE、ROLLUP、および GROUPING SETS は CHECKSUM_AGG 関数をサポートしていません。 |
複合要素と連結要素
GROUPING SETS リストで、内側のかっこに囲まれている複数の列は単一のセットとして処理されます。たとえば、GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4) という句では、Column1 と Column2 は 1 つの列と見なされます。複合要素と共に GROUPING SETS を使用する方法の例については、後の「例 H」を参照してください。
GROUPING SETS のリストで、内側のかっこ内に複数のセットをコンマで区切って指定した場合は、それらのセットの出力が連結されます。結果セットは、指定したグループ セットのクロス積 (デカルト積) になります。連結された ROLLUP 演算子と共に GROUP BY を使用する方法については、後の「例 D」を参照してください。
OLAP ディメンションと比較した ROLLUP および CUBE
ROLLUP 演算子または CUBE 演算子を使用するクエリは、OLAP アプリケーションと同じ結果セットを生成し、OLAP アプリケーションと同じ計算を実行する場合があります。CUBE 演算子は、クロス集計レポートに使用できる結果セットを生成します。ROLLUP 演算子は、OLAP ディメンションまたは階層と同等のものを計算できます。
たとえば、年、月、および日のレベルまたは属性を持つ時間ディメンションが与えられている場合、次の ROLLUP 演算では以下のグループが生成されます。
演算 |
グループ |
---|---|
|
year, month, day year, month year () |
都道府県と市区町村のレベルを持つ場所ディメンションが、年、月、日の時間ディメンション レベルと結合されている場合、次の ROLLUP 演算は以下のグループを出力します。
演算 |
グループ |
---|---|
|
region, city, year, month, day region, city, year, month region, city, year region, city region, year, month, day region, year, month region, year region year, month, day year, month year () |
場所ディメンションと時間ディメンションを含む同じレベルの CUBE 演算は、次のグループを出力します。
演算 |
グループ |
---|---|
|
region, city, year, month, day region, city, year, month region, city, year region, city region, city, month, day region, city, month region, city, day region, city, year, day region, city, day region, year, month, day region, year, month region, year region, month, day region, month region, year, day region, day region city, year, month, day city, year, month city, year city, month, day city, month city, year, day city, day year, month, day year, month year year, day month, day month day () |
結果セット内の NULL
GROUP BY 演算子によって生成された結果セット内では、NULL は次のような用途があります。
グループ列に NULL が含まれている場合、すべての NULL 値は等しいものと見なされ、1 つの NULL グループに配置されます。
列が 1 行に集計される場合、その列の値は NULL として示されます。
次の例では、GROUPING 関数を使用して NULL の 2 種類の用途を示します。UNKNOWN は、列内の NULL がグループ化されている行内の NULL を置換します。ALL は、列が集計に含まれていることが NULL によって示されている列内の NULL を置換します。
USE tempdb;
GO
CREATE TABLE dbo.GroupingNULLS (
Store nvarchar(19)
,SaleYear nvarchar(4)
,SaleMonth nvarchar (7))
INSERT INTO dbo.GroupingNULLS VALUES
(NULL,NULL,'January')
,(NULL,'2002',NULL)
,(NULL,NULL,NULL)
,('Active Cycling',NULL ,'January')
,('Active Cycling','2002',NULL)
,('Active Cycling',NULL ,NULL)
,('Active Cycling',NULL,'January')
,('Active Cycling','2003','Febuary')
,('Active Cycling','2003',NULL)
,('Mountain Bike Store','2002','January')
,('Mountain Bike Store','2002',NULL)
,('Mountain Bike Store',NULL,NULL)
,('Mountain Bike Store','2003','January')
,('Mountain Bike Store','2003','Febuary')
,('Mountain Bike Store','2003','March');
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
,COUNT(*) AS Count
FROM dbo.GroupingNULLS
GROUP BY ROLLUP(Store, SaleYear, SaleMonth);
以下に結果セットを示します。
Store |
SalesYear |
SalesMonth |
Count |
---|---|---|---|
Unknown |
Unknown |
Unknown |
1 |
Unknown |
Unknown |
January |
1 |
Unknown |
Unknown |
ALL |
2 |
Unknown |
2002 |
Unknown |
1 |
Unknown |
2002 |
ALL |
1 |
Unknown |
ALL |
ALL |
3 |
Active Cycling |
Unknown |
Unknown |
1 |
Active Cycling |
Unknown |
January |
2 |
Active Cycling |
Unknown |
ALL |
3 |
Active Cycling |
2002 |
Unknown |
1 |
Active Cycling |
2002 |
ALL |
1 |
Active Cycling |
2003 |
Unknown |
1 |
Active Cycling |
2003 |
Febuary |
1 |
Active Cycling |
2003 |
ALL |
2 |
Active Cycling |
ALL |
ALL |
6 |
Mountain Bike Store |
Unknown |
Unknown |
1 |
Mountain Bike Store |
Unknown |
ALL |
1 |
Mountain Bike Store |
2002 |
Unknown |
1 |
Mountain Bike Store |
2002 |
January |
1 |
Mountain Bike Store |
2002 |
ALL |
2 |
Mountain Bike Store |
2003 |
Febuary |
1 |
Mountain Bike Store |
2003 |
January |
1 |
Mountain Bike Store |
2003 |
March |
1 |
Mountain Bike Store |
2003 |
ALL |
3 |
Mountain Bike Store |
ALL |
ALL |
6 |
ALL |
ALL |
ALL |
15 |
例
このセクションの例では、SUM 集計関数を使用して、結果セットを比較できるようにしています。その他の集計関数を使用して異なるサマリーを計算する場合もあります。
A. 単純な GROUP BY を使用する
次の例では、単純な GROUP BY によって、例 B ~ K の結果セットと比較するための結果セットが返されます。これらの例では、同じ SELECT ステートメントを指定して GROUP BY 演算子を使用しています。
USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
,S.Name AS N'Store', H.SalesPersonID
,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
AND T.CountryRegionCode IN(N'DE', N'FR')
AND H.SalesPersonID IN(284, 286, 289)
AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID
ORDER BY T.[Group], T.CountryRegionCode
,S.Name,H.SalesPersonID;
以下に結果セットを示します。
Region |
Country |
Store |
SalesPersonID |
Total Sales |
---|---|---|---|---|
Europe |
DE |
Versatile Sporting Goods Company |
284 |
859.232 |
Europe |
DE |
Versatile Sporting Goods Company |
289 |
17691.83 |
Europe |
FR |
Spa and Exercise Outfitters |
284 |
32774.36 |
Europe |
FR |
Spa and Exercise Outfitters |
286 |
246272.4 |
B. GROUP BY ROLLUP を使用する
次の例では、ROLLUP 演算子は以下のグループを含む結果セットを返します。
Region、Country、Store、SalesPersonID
Region、Country、Store
Region、Country
Region
総計
ROLLUP によって生成されるグループの数は、ROLLUP リスト内の列数に総計グループを足したものと同じになります。1 つのグループの行数は、そのグループの列内に含まれている一意の値の組み合わせの数によって決定します。
USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
,S.Name AS N'Store', H.SalesPersonID
,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
AND T.CountryRegionCode IN(N'DE', N'FR')
AND H.SalesPersonID IN(284, 286, 289)
AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY ROLLUP(
T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;
以下に結果セットを示します。
Region |
Country |
Store |
SalesPersonID |
Total Sales |
---|---|---|---|---|
NULL |
NULL |
NULL |
NULL |
297597.8 |
Europe |
NULL |
NULL |
NULL |
297597.8 |
Europe |
DE |
NULL |
NULL |
18551.07 |
Europe |
DE |
Versatile Sporting Goods Company |
NULL |
18551.07 |
Europe |
DE |
Versatile Sporting Goods Company |
284 |
859.232 |
Europe |
DE |
Versatile Sporting Goods Company |
289 |
17691.83 |
Europe |
FR |
NULL |
NULL |
279046.8 |
Europe |
FR |
Spa and Exercise Outfitters |
NULL |
279046.8 |
Europe |
FR |
Spa and Exercise Outfitters |
284 |
32774.36 |
Europe |
FR |
Spa and Exercise Outfitters |
286 |
246272.4 |
C. 列の順序を逆にして GROUP BY ROLLUP を使用する
次の例では、ROLLUP 演算子は以下のグループを含む結果セットを返します。
SalesPersonID、Store、Country、Region
SalesPersonID、Store、Country
SalesPersonID、Store
SalesPersonID
総計
ROLLUP リストに指定されている列は例 B と同じですが、列の指定順序が逆になっています。列は右から左に収集されるので、順序がグループに影響を及ぼしています。結果セット内の行数は、列の順序に応じて異なる場合があります。
USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
,S.Name AS N'Store', H.SalesPersonID
,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
AND T.CountryRegionCode IN(N'DE', N'FR')
AND H.SalesPersonID IN(284, 286, 289)
AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY ROLLUP(
H.SalesPersonID, S.Name, T.CountryRegionCode, T.[Group])
ORDER BY H.SalesPersonID, S.Name, T.CountryRegionCode, T.[Group];
以下に結果セットを示します。
Region |
Country |
Store |
SalesPersonID |
Total Sales |
---|---|---|---|---|
NULL |
NULL |
NULL |
NULL |
297597.8 |
NULL |
NULL |
NULL |
284 |
33633.59 |
NULL |
NULL |
Spa and Exercise Outfitters |
284 |
32774.36 |
NULL |
FR |
Spa and Exercise Outfitters |
284 |
32774.36 |
Europe |
FR |
Spa and Exercise Outfitters |
284 |
32774.36 |
NULL |
NULL |
Versatile Sporting Goods Company |
284 |
859.232 |
NULL |
DE |
Versatile Sporting Goods Company |
284 |
859.232 |
Europe |
DE |
Versatile Sporting Goods Company |
284 |
859.232 |
NULL |
NULL |
NULL |
286 |
246272.4 |
NULL |
NULL |
Spa and Exercise Outfitters |
286 |
246272.4 |
NULL |
FR |
Spa and Exercise Outfitters |
286 |
246272.4 |
Europe |
FR |
Spa and Exercise Outfitters |
286 |
246272.4 |
NULL |
NULL |
NULL |
289 |
17691.83 |
NULL |
NULL |
Versatile Sporting Goods Company |
289 |
17691.83 |
NULL |
DE |
Versatile Sporting Goods Company |
289 |
17691.83 |
Europe |
DE |
Versatile Sporting Goods Company |
289 |
17691.83 |
D. 連結された ROLLUP 演算に GROUP BY を使用する
次の例では、2 つの ROLLUP 演算のクロス積が返されます。
USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
,DATEPART(yyyy,OrderDate) AS 'Year'
,DATEPART(mm,OrderDate) AS 'Month'
,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
AND T.CountryRegionCode IN(N'DE', N'FR')
AND DATEPART(yyyy,OrderDate) = '2004'
GROUP BY
ROLLUP(T.[Group], T.CountryRegionCode)
,ROLLUP(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate))
ORDER BY T.[Group], T.CountryRegionCode
,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);
以下に結果セットを示します。
Region |
Country |
Year |
Month |
Total Sales |
---|---|---|---|---|
NULL |
NULL |
NULL |
NULL |
3031201 |
NULL |
NULL |
2004 |
NULL |
3031201 |
NULL |
NULL |
2004 |
1 |
208553.6 |
NULL |
NULL |
2004 |
2 |
819466.6 |
NULL |
NULL |
2004 |
3 |
298579.1 |
NULL |
NULL |
2004 |
4 |
294427.7 |
NULL |
NULL |
2004 |
5 |
1070679 |
NULL |
NULL |
2004 |
6 |
339495.1 |
Europe |
NULL |
NULL |
NULL |
3031201 |
Europe |
NULL |
2004 |
NULL |
3031201 |
Europe |
NULL |
2004 |
1 |
208553.6 |
Europe |
NULL |
2004 |
2 |
819466.6 |
Europe |
NULL |
2004 |
3 |
298579.1 |
Europe |
NULL |
2004 |
4 |
294427.7 |
Europe |
NULL |
2004 |
5 |
1070679 |
Europe |
NULL |
2004 |
6 |
339495.1 |
Europe |
DE |
NULL |
NULL |
1196260 |
Europe |
DE |
2004 |
NULL |
1196260 |
Europe |
DE |
2004 |
1 |
155066.2 |
Europe |
DE |
2004 |
2 |
197801.8 |
Europe |
DE |
2004 |
3 |
180977.7 |
Europe |
DE |
2004 |
4 |
222683.4 |
Europe |
DE |
2004 |
5 |
258962 |
Europe |
DE |
2004 |
6 |
180769.1 |
Europe |
FR |
NULL |
NULL |
1834941 |
Europe |
FR |
2004 |
NULL |
1834941 |
Europe |
FR |
2004 |
1 |
53487.37 |
Europe |
FR |
2004 |
2 |
621664.9 |
Europe |
FR |
2004 |
3 |
117601.4 |
Europe |
FR |
2004 |
4 |
71744.28 |
Europe |
FR |
2004 |
5 |
811716.9 |
Europe |
FR |
2004 |
6 |
158726 |
E. GROUP BY CUBE を使用する
次の例では、CUBE 演算子は、CUBE リスト内の列のすべての可能な組み合わせに対する 1 つのグループおよび総計グループを含む結果セットを返します。
USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
,S.Name AS N'Store', H.SalesPersonID
,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
AND T.CountryRegionCode IN(N'DE', N'FR')
AND H.SalesPersonID IN(284, 286, 289)
AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY CUBE(
T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;
以下に結果セットを示します。
Region |
Country |
Store |
SalesPersonID |
Total Sales |
---|---|---|---|---|
NULL |
NULL |
NULL |
NULL |
297597.8 |
NULL |
NULL |
NULL |
284 |
33633.59 |
NULL |
NULL |
NULL |
286 |
246272.4 |
NULL |
NULL |
NULL |
289 |
17691.83 |
NULL |
NULL |
Spa and Exercise Outfitters |
NULL |
279046.8 |
NULL |
NULL |
Spa and Exercise Outfitters |
284 |
32774.36 |
NULL |
NULL |
Spa and Exercise Outfitters |
286 |
246272.4 |
NULL |
NULL |
Versatile Sporting Goods Company |
NULL |
18551.07 |
NULL |
NULL |
Versatile Sporting Goods Company |
284 |
859.232 |
NULL |
NULL |
Versatile Sporting Goods Company |
289 |
17691.83 |
NULL |
DE |
NULL |
NULL |
18551.07 |
NULL |
DE |
NULL |
284 |
859.232 |
NULL |
DE |
NULL |
289 |
17691.83 |
NULL |
DE |
Versatile Sporting Goods Company |
NULL |
18551.07 |
NULL |
DE |
Versatile Sporting Goods Company |
284 |
859.232 |
NULL |
DE |
Versatile Sporting Goods Company |
289 |
17691.83 |
NULL |
FR |
NULL |
NULL |
279046.8 |
NULL |
FR |
NULL |
284 |
32774.36 |
NULL |
FR |
NULL |
286 |
246272.4 |
NULL |
FR |
Spa and Exercise Outfitters |
NULL |
279046.8 |
NULL |
FR |
Spa and Exercise Outfitters |
284 |
32774.36 |
NULL |
FR |
Spa and Exercise Outfitters |
286 |
246272.4 |
Europe |
NULL |
NULL |
NULL |
297597.8 |
Europe |
NULL |
NULL |
284 |
33633.59 |
Europe |
NULL |
NULL |
286 |
246272.4 |
Europe |
NULL |
NULL |
289 |
17691.83 |
Europe |
NULL |
Spa and Exercise Outfitters |
NULL |
279046.8 |
Europe |
NULL |
Spa and Exercise Outfitters |
284 |
32774.36 |
Europe |
NULL |
Spa and Exercise Outfitters |
286 |
246272.4 |
Europe |
NULL |
Versatile Sporting Goods Company |
NULL |
18551.07 |
Europe |
NULL |
Versatile Sporting Goods Company |
284 |
859.232 |
Europe |
NULL |
Versatile Sporting Goods Company |
289 |
17691.83 |
Europe |
DE |
NULL |
NULL |
18551.07 |
Europe |
DE |
NULL |
284 |
859.232 |
Europe |
DE |
NULL |
289 |
17691.83 |
Europe |
DE |
Versatile Sporting Goods Company |
NULL |
18551.07 |
Europe |
DE |
Versatile Sporting Goods Company |
284 |
859.232 |
Europe |
DE |
Versatile Sporting Goods Company |
289 |
17691.83 |
Europe |
FR |
NULL |
NULL |
279046.8 |
Europe |
FR |
NULL |
284 |
32774.36 |
Europe |
FR |
NULL |
286 |
246272.4 |
Europe |
FR |
Spa and Exercise Outfitters |
NULL |
279046.8 |
Europe |
FR |
Spa and Exercise Outfitters |
284 |
32774.36 |
Europe |
FR |
Spa and Exercise Outfitters |
286 |
246272.4 |
F. 複合要素と共に CUBE を使用する
次の例では、CUBE 演算子は、CUBE リスト内の列のすべての可能な組み合わせに対する 1 つのグループおよび総計グループを含む結果セットを返します。
この演算子は、グループ化された列 (T.[Group], T.CountryRegionCode) および (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)) をそれぞれ 1 つの列として処理します。
USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
,DATEPART(yyyy,OrderDate) AS 'Year'
,DATEPART(mm,OrderDate) AS 'Month'
,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
AND T.CountryRegionCode IN(N'DE', N'FR')
AND DATEPART(yyyy,OrderDate) = '2004'
GROUP BY CUBE(
(T.[Group], T.CountryRegionCode)
,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);
以下に結果セットを示します。
Region |
Country |
Year |
Month |
Total Sales |
---|---|---|---|---|
NULL |
NULL |
NULL |
NULL |
3031201 |
NULL |
NULL |
2004 |
1 |
208553.6 |
NULL |
NULL |
2004 |
2 |
819466.6 |
NULL |
NULL |
2004 |
3 |
298579.1 |
NULL |
NULL |
2004 |
4 |
294427.7 |
NULL |
NULL |
2004 |
5 |
1070679 |
NULL |
NULL |
2004 |
6 |
339495.1 |
Europe |
DE |
NULL |
NULL |
1196260 |
Europe |
DE |
2004 |
1 |
155066.2 |
Europe |
DE |
2004 |
2 |
197801.8 |
Europe |
DE |
2004 |
3 |
180977.7 |
Europe |
DE |
2004 |
4 |
222683.4 |
Europe |
DE |
2004 |
5 |
258962 |
Europe |
DE |
2004 |
6 |
180769.1 |
Europe |
FR |
NULL |
NULL |
1834941 |
Europe |
FR |
2004 |
1 |
53487.37 |
Europe |
FR |
2004 |
2 |
621664.9 |
Europe |
FR |
2004 |
3 |
117601.4 |
Europe |
FR |
2004 |
4 |
71744.28 |
Europe |
FR |
2004 |
5 |
811716.9 |
Europe |
FR |
2004 |
6 |
158726 |
G. GROUPING SETS と共に GROUP BY を使用する
次の例では、GROUPING SETS 演算子に 4 つのグループが指定されています。これらは、SELECT リストの列にそれぞれ対応しています。この演算子は、一意の各値に対応する 1 行を Region、Country、Store、および SalesPersonID 列に返します。
USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
,S.Name AS N'Store', H.SalesPersonID
,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
AND T.CountryRegionCode IN(N'DE', N'FR')
AND H.SalesPersonID IN(284, 286, 289)
AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS
(T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;
以下に結果セットを示します。
Region |
Country |
Store |
SalesPersonID |
Total Sales |
---|---|---|---|---|
NULL |
NULL |
NULL |
284 |
33633.59 |
NULL |
NULL |
NULL |
286 |
246272.4 |
NULL |
NULL |
NULL |
289 |
17691.83 |
NULL |
NULL |
Spa and Exercise Outfitters |
NULL |
279046.8 |
NULL |
NULL |
Versatile Sporting Goods Company |
NULL |
18551.07 |
NULL |
DE |
NULL |
NULL |
18551.07 |
NULL |
FR |
NULL |
NULL |
279046.8 |
Europe |
NULL |
NULL |
NULL |
297597.8 |
H. 複合要素と共に GROUPING SETS を使用する
次の例では、GROUPING SETS リストに 2 つの複合要素 (T.[Group], T.CountryRegionCode) と (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)) が含まれています。各複合要素は 1 つの列として処理されます。
USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
,DATEPART(yyyy,OrderDate) AS 'Year'
,DATEPART(mm,OrderDate) AS 'Month'
,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
AND T.CountryRegionCode IN(N'DE', N'FR')
AND DATEPART(yyyy,OrderDate) = '2004'
GROUP BY GROUPING SETS(
(T.[Group], T.CountryRegionCode)
,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);
以下に結果セットを示します。
Region |
Country |
Year |
Month |
Total Sales |
---|---|---|---|---|
NULL |
NULL |
2004 |
1 |
208553.6 |
NULL |
NULL |
2004 |
2 |
819466.6 |
NULL |
NULL |
2004 |
3 |
298579.1 |
NULL |
NULL |
2004 |
4 |
294427.7 |
NULL |
NULL |
2004 |
5 |
1070679 |
NULL |
NULL |
2004 |
6 |
339495.1 |
Europe |
DE |
NULL |
NULL |
1196260 |
Europe |
FR |
NULL |
NULL |
1834941 |
I. 複数の GROUPING SETS と共に GROUP BY を使用する
次の例では、GROUPING SETS リストに 5 つの要素があります。結果セットには、次の要素に対応する 1 行があります。
Region 列の値と Country 列の値のすべての一意な組み合わせ
Store 列内の一意の各値
SalesPersonID 列の値と Region 列の値のすべての一意な組み合わせ
SalesPersonID 列内の一意の各値
総計
USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
,S.Name AS N'Store', H.SalesPersonID
,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
AND T.CountryRegionCode IN(N'DE', N'FR')
AND H.SalesPersonID IN(284, 286, 289)
AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
(T.[Group], T.CountryRegionCode)
,(S.Name)
,(H.SalesPersonID,T.[Group])
,(H.SalesPersonID)
,())
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;
以下に結果セットを示します。
Region |
Country |
Store |
SalesPersonID |
Total Sales |
---|---|---|---|---|
NULL |
NULL |
NULL |
NULL |
297597.8 |
NULL |
NULL |
NULL |
284 |
33633.59 |
NULL |
NULL |
NULL |
286 |
246272.4 |
NULL |
NULL |
NULL |
289 |
17691.83 |
NULL |
NULL |
Spa and Exercise Outfitters |
NULL |
279046.8 |
NULL |
NULL |
Versatile Sporting Goods Company |
NULL |
18551.07 |
Europe |
NULL |
NULL |
284 |
33633.59 |
Europe |
NULL |
NULL |
286 |
246272.4 |
Europe |
NULL |
NULL |
289 |
17691.83 |
Europe |
DE |
NULL |
NULL |
18551.07 |
Europe |
FR |
NULL |
NULL |
279046.8 |
J. GROUP BY リストの一部の ROLLUP と共に GROUPING SETS を使用する
次の例では、GROUPING SETS リストには列 T.[Group] および T.CountryRegionCode のグループと列 S.Name および H.SalesPersonID の ROLLUP が含まれています。
USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
,S.Name AS N'Store', H.SalesPersonID
,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
AND T.CountryRegionCode IN(N'DE', N'FR')
AND H.SalesPersonID IN(284, 286, 289)
AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
T.[Group], T.CountryRegionCode
,ROLLUP(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;
以下に結果セットを示します。
Region |
Country |
Store |
SalesPersonID |
Total Sales |
---|---|---|---|---|
NULL |
NULL |
NULL |
NULL |
297597.8 |
NULL |
NULL |
Spa and Exercise Outfitters |
NULL |
279046.8 |
NULL |
NULL |
Spa and Exercise Outfitters |
284 |
32774.36 |
NULL |
NULL |
Spa and Exercise Outfitters |
286 |
246272.4 |
NULL |
NULL |
Versatile Sporting Goods Company |
NULL |
18551.07 |
NULL |
NULL |
Versatile Sporting Goods Company |
284 |
859.232 |
NULL |
NULL |
Versatile Sporting Goods Company |
289 |
17691.83 |
NULL |
DE |
NULL |
NULL |
18551.07 |
NULL |
FR |
NULL |
NULL |
279046.8 |
Europe |
NULL |
NULL |
NULL |
297597.8 |
K. GROUP BY リストの一部の CUBE と共に GROUPING SETS を使用する
次の例では、GROUPING SETS リストには列 T.[Group] および T.CountryRegionCode のグループと列 S.Name および H.SalesPersonID の CUBE が含まれています。
USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
,S.Name AS N'Store', H.SalesPersonID
,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
AND T.CountryRegionCode IN(N'DE', N'FR')
AND H.SalesPersonID IN(284, 286, 289)
AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
T.[Group], T.CountryRegionCode
,CUBE(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;
以下に結果セットを示します。
Region |
Country |
Store |
SalesPersonID |
Total Sales |
---|---|---|---|---|
NULL |
NULL |
NULL |
NULL |
297597.8 |
NULL |
NULL |
NULL |
284 |
33633.59 |
NULL |
NULL |
NULL |
286 |
246272.4 |
NULL |
NULL |
NULL |
289 |
17691.83 |
NULL |
NULL |
Spa and Exercise Outfitters |
NULL |
279046.8 |
NULL |
NULL |
Spa and Exercise Outfitters |
284 |
32774.36 |
NULL |
NULL |
Spa and Exercise Outfitters |
286 |
246272.4 |
NULL |
NULL |
Versatile Sporting Goods Company |
NULL |
18551.07 |
NULL |
NULL |
Versatile Sporting Goods Company |
284 |
859.232 |
NULL |
NULL |
Versatile Sporting Goods Company |
289 |
17691.83 |
NULL |
DE |
NULL |
NULL |
18551.07 |
NULL |
FR |
NULL |
NULL |
279046.8 |
Europe |
NULL |
NULL |
NULL |
297597.8 |