SELECT - GROUP BY- Transact-SQL
適用対象: SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Microsoft Fabric のSQL 分析エンドポイント
Microsoft Fabric のウェアハウス
クエリ結果を行のグループに分割する SELECT ステートメント句。これは通常、グループごとに 1 つまたは複数の集計を実行することで行われます。 SELECT ステートメントでは、グループごとに 1 つの行が返されます。
構文
-- Syntax for SQL Server and Azure SQL Database
-- ISO-Compliant Syntax
GROUP BY {
column-expression
| ROLLUP ( <group_by_expression> [ ,...n ] )
| CUBE ( <group_by_expression> [ ,...n ] )
| GROUPING SETS ( <grouping_set> [ ,...n ] )
| () --calculates the grand total
} [ ,...n ]
<group_by_expression> ::=
column-expression
| ( column-expression [ ,...n ] )
<grouping_set> ::=
() --calculates the grand total
| <grouping_set_item>
| ( <grouping_set_item> [ ,...n ] )
<grouping_set_item> ::=
<group_by_expression>
| ROLLUP ( <group_by_expression> [ ,...n ] )
| CUBE ( <group_by_expression> [ ,...n ] )
-- For backward compatibility only.
-- Non-ISO-Compliant Syntax for SQL Server and Azure SQL Database
GROUP BY {
ALL column-expression [ ,...n ]
| column-expression [ ,...n ] WITH { CUBE | ROLLUP }
}
-- Syntax for Azure Synapse Analytics
GROUP BY {
column-name [ WITH (DISTRIBUTED_AGG) ]
| column-expression
| ROLLUP ( <group_by_expression> [ ,...n ] )
} [ ,...n ]
-- Syntax for Parallel Data Warehouse
GROUP BY {
column-name [ WITH (DISTRIBUTED_AGG) ]
| column-expression
} [ ,...n ]
Note
SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。
引数
column-expression
列を指定するか、列で集計計算以外を指定します。 この列は、テーブル、派生テーブル、またはビューに属することができます。 この列は、SELECT ステートメントの FROM 句に表示する必要がありますが、SELECT リストに表示する必要はありません。
有効な式については、式に関するページを参照してください。
この列は、SELECT ステートメントの FROM 句に表示する必要がありますが、SELECT リストに表示する必要はありません。 しかし、<select> リスト内にある非集計式のテーブルやビューの列は、それぞれ GROUP BY リストに含まれている必要があります。
次のようなステートメントは使用できます。
SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA, ColumnB;
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA, ColumnB;
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA + ColumnB;
SELECT ColumnA + ColumnB + constant FROM T GROUP BY ColumnA, ColumnB;
次のようなステートメントは使用できません。
SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA + ColumnB;
SELECT ColumnA + constant + ColumnB FROM T GROUP BY ColumnA + ColumnB;
列の式には次を含めることはできません。
- SELECT リストで定義されている列の別名。 FROM 句で定義されている派生テーブルの列の別名を使用できます。
- text、ntext、または image 型の列。 ただし、text、ntext、または image の列は、有効なデータ型の値を返す関数の引数として使用できます。 たとえば、式では、SUBSTRING() および CAST() を使用できます。 これは、HAVING 句内の式にも適用されます。
- xml データ型メソッド。 xml データ型メソッドを使用するユーザー定義関数を含めることができます。 xml データ型メソッドを使用する計算列を含めることができます。
- サブクエリ。 エラー 144 が返されます。
- インデックス付きビューの列。
GROUP BY column-expression [ ,...n ]
1 つまたは複数の列の式のリスト内の値に従って SELECT ステートメントの結果をグループ化します。
たとえば、このクエリは、Country (国)、Region (地域)、および Sales (売上) の列を持つ Sales テーブルを作成します。 4 つの行を挿入し、そのうちの 2 行は Country と Region の一致する値を持ちます。
CREATE TABLE Sales ( Country VARCHAR(50), Region VARCHAR(50), Sales INT );
INSERT INTO sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO sales VALUES (N'United States', N'Montana', 100);
Sales テーブルには、次の行が含まれています。
Country | Region | 売上 |
---|---|---|
Canada | Alberta | 100 |
Canada | British Columbia | 200 |
Canada | British Columbia | 300 |
United States | モンタナ | 100 |
次のクエリでは、Country と Region をグループ化し、値の組み合わせごとの合計を返します。
SELECT Country, Region, SUM(sales) AS TotalSales
FROM Sales
GROUP BY Country, Region;
Country と Region には値の 3 つの組み合わせがあるため、クエリ結果には 3 つの行ができます。 Canada と British Columbia の TotalSales は、2 つの行の合計です。
Country | リージョン | TotalSales |
---|---|---|
Canada | Alberta | 100 |
Canada | British Columbia | 500 |
United States | モンタナ | 100 |
GROUP BY ROLLUP
列の式の組み合わせごとにグループを作成します。 さらに、結果を小計と総計に "ロール アップ" します。 これを行うため、右から左に移動して、列の式の数を減らし、それに対してグループと集計を作成します。
列の順序は ROLLUP 出力に影響を与えます。結果セット内の行数に影響を与えることもあります。
たとえば、GROUP BY ROLLUP (col1, col2, col3, col4)
は次の一覧の列の式の組み合わせごとにグループを作成します。
- col1, col2, col3, col4
- col1, col2, col3, NULL
- col1, col2, NULL, NULL
- col1, NULL, NULL, NULL
- NULL, NULL, NULL, NULL --これが総計です
前の例のテーブルを使用して、このコードは、単純な GROUP BY の代わりに GROUP BY ROLLUP 操作を実行します。
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region);
クエリ結果は、ROLLUP なしの単純な GROUP BY と同じ集計になります。 さらに、Country の値ごとの小計を作成します。 最後に、すべての行の総計を示します。 結果は次のようになります。
Country | リージョン | TotalSales |
---|---|---|
Canada | Alberta | 100 |
Canada | British Columbia | 500 |
Canada | NULL | 600 |
United States | モンタナ | 100 |
United States | NULL | 100 |
NULL | NULL | 700 |
GROUP BY CUBE ( )
GROUP BY CUBE では、列の可能なすべての組み合わせのグループを作成します。 GROUP BY CUBE (a, b) の場合、結果は (a, b)、(NULL, b)、(a, NULL)、および (NULL, NULL) の一意の値のグループになります。
このコードは、前の例のテーブルを使用して、Country と Region で GROUP BY CUBE 操作を実行します。
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);
クエリの結果は、(Country, Region)、(NULL, Region)、(Country, NULL)、および (NULL, NULL) の一意の値のグループになります。 結果は、次のようになります。
Country | リージョン | TotalSales |
---|---|---|
Canada | Alberta | 100 |
NULL | Alberta | 100 |
Canada | British Columbia | 500 |
NULL | British Columbia | 500 |
United States | モンタナ | 100 |
NULL | モンタナ | 100 |
NULL | NULL | 700 |
Canada | NULL | 600 |
United States | NULL | 100 |
GROUP BY GROUPING SETS ( )
GROUPING SETS オプションを使用すると、複数の GROUP BY 句を 1 つの GROUP BY 句に統合できます。 結果は、指定したグループの UNION ALL と同じになります。
たとえば、GROUP BY ROLLUP (Country, Region)
と GROUP BY GROUPING SETS ( ROLLUP (Country, Region) )
は同じ結果を返します。
GROUPING SETS に 2 つ以上の要素がある場合、結果はそれらの要素の和集合になります。 この例では、Country と Region の ROLLUP と CUBE の結果の和集合を返します。
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( ROLLUP (Country, Region), CUBE (Country, Region) );
結果は、この 2 つの GROUP BY ステートメントの和集合を返すクエリと同じです。
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region)
UNION ALL
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);
SQL では、GROUPING SETS リストに対して生成された重複するグループの統合は行われません。 たとえば、GROUP BY ( (), CUBE (Country, Region) )
では、両方の要素が総計の行を返し、両方の行が結果に表示されます。
GROUP BY ()
総計を生成する空のグループを指定します。 これは、GROUPING SET の要素の 1 つとして役立ちます。 たとえば、このステートメントにより、各国や地域の売上合計と、すべての国や地域の総計が提供されます。
SELECT Country, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( Country, () );
GROUP BY ALL column-expression [ ,...n ]
適用対象:SQL Server と Azure SQL Database
Note
この構文は、旧バージョンとの互換性を維持するためだけに提供されます。 将来のバージョンでは削除される予定です。 新規の開発作業ではこの構文を使用しないようにし、現在この構文を使用しているアプリケーションは修正することを検討してください。
WHERE 句の検索条件を満たしているかどうかに関係なく、結果にすべてのグループを含めることを指定します。 検索条件を満たしていないグループの集計は NULL になります。
GROUP BY ALL:
- クエリ内に WHERE 句がある場合、リモート テーブルにアクセスするクエリでは、サポートされません。
- FILESTREAM 属性を持つ列では失敗します。
GROUP BY column-expression [ ,...n ] WITH { CUBE | ROLLUP }
適用対象:SQL Server と Azure SQL Database
Note
この構文は、旧バージョンとの互換性を維持するためだけに提供されます。 新規の開発作業ではこの構文を使用しないようにし、現在この構文を使用しているアプリケーションは修正することを検討してください。
WITH (DISTRIBUTED_AGG)
適用対象: Azure Synapse Analytics および Analytics Platform System (PDW)
DISTRIBUTED_AGG クエリ ヒントは、超並列処理 (MPP) システムで、特定の列に対し、集計を実行する前にテーブルを強制的に再配布します。 DISTRIBUTED_AGG クエリ ヒントを持つことができるのは、GROUP BY 句内で 1 つの列だけです。 クエリが完了したら、再配布されたテーブルは破棄されます。 元のテーブルは変更されません。
注:DISTRIBUTED_AGG クエリ ヒントは、Analytics Platform System (PDW) の旧バージョンとの下位互換性を保つために提供されており、ほとんどのクエリでパフォーマンスが向上することはありません。 既定では、MPP は集計のパフォーマンスを向上させるために必要なデータを既に再分配しています。
全般的な解説
GROUP BY が SELECT ステートメントと連携するしくみ
SELECT リスト:
- ベクター集計。 SELECT リストに集計関数が含まれている場合は、GROUP BY によって各グループの集計値が計算されます。 これらはベクター集計値と呼ばれます。
- Distinct 集計。 AVG (DISTINCT column_name)、COUNT (DISTINCT column_name)、および UM (DISTINCT column_name) は ROLLUP、CUBE、および GROUPING SETS でサポートされています。
WHERE 句:
- SQL では、WHERE 句の条件と一致しない行は、グループ化操作の実行前に削除されます。
HAVING 句:
- SQL では、HAVING 句を使用して、結果セット内のグループをフィルターできます。
ORDER BY 句:
- 結果セットを並べ替えるには、ORDER BY 句を使用します。 GROUP BY 句では、結果セットの並べ替えが行われません。
NULL 値:
- グループ列に NULL 値が含まれている場合、すべての NULL 値は等しいものと見なされ、1 つのグループにまとめられます。
制限事項と制約事項
適用対象:SQL Server (2008 以降) および Azure Synapse Analytics
最大容量
ROLLUP、CUBE、または GROUPING SETS を使用する GROUP BY 句の場合、式の最大数は 32 です。 グループの最大数は 4096 (212) です。 次の例は、GROUP BY 句に 4096 を超えるグループがあるために失敗します。
次の例では、4097 (212 + 1) 個のグループ化セットが生成され、失敗します。
GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )
次の例では、4097 (212 + 1) 個のグループが生成され、失敗します。
CUBE ()
と()
の両方のグループ化セットで総計行が生成され、重複したグループ化セットは削除されません。GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())
この例では、下位互換性のある構文を使用します。 8192 (213) 個のグループ化セットが生成され、失敗します。
GROUP BY CUBE (a1, ..., a13) GROUP BY a1, ..., a13 WITH CUBE
CUBE または ROLLUP を含まない下位互換性のある GROUP BY 句では、項目ごとのグループの数はクエリにかかわる GROUP BY 列のサイズ、集計列、集計値によって制限されます。 この数制限は、クエリの中間結果を保持するために中間作業テーブル上に必要な 8,060 バイトを基本にしています。 CUBE または ROLLUP を指定している場合は、最大 12 個のグループ化式を使用できます。
ISO および ANSI SQL-2006 の GROUP BY 機能のサポート
GROUP BY 句では、SQL-2006 標準規格に含まれているすべての GROUP BY 機能をサポートしています。ただし、次のような構文上の例外があります。
GROUP BY 句では、明示的な GROUPING SETS リストに含まれていないグループ化セットは使用できません。 たとえば、
GROUP BY Column1, (Column2, ...ColumnN
) は標準規格で使用できますが、Transact-SQL では使用できません。 Transact SQL は、同じ意味のGROUP BY C1, GROUPING SETS ((Column2, ...ColumnN))
とGROUP BY Column1, Column2, ... ColumnN
をサポートしています。 これらは、前に示したGROUP BY
の例と意味的に同じです。 これには、GROUP BY Column1, (Column2, ...ColumnN
) が意味的に異なるGROUP BY C1, GROUPING SETS ((Column2, ...ColumnN))
と誤って解釈される可能性を回避する目的があります。グループ化セット内でグループ化セットを使用することはできません。 たとえば、
GROUP BY GROUPING SETS (A1, A2,...An, GROUPING SETS (C1, C2, ...Cn))
は SQL-2006 標準規格で使用できますが、Transact-SQL では使用できません。 Transact-SQL では、最初の GROUP BY の例と意味的に同じで、構文がより明確なGROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn )
またはGROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) )
を使用できます。GROUP BY [ALL/DISTINCT] は、列の式を含む単純な GROUP BY 句でのみ使用できます。 GROUPING SETS、ROLLUP、CUBE、WITH CUBE、WITH ROLLUP のコンストラクトでは使用できません。 ALL は既定値であり暗黙的です。 また、下位互換性のある構文でのみ使用できます。
サポートされる GROUP BY 機能の比較
次の表に、SQL のバージョンに基づいてサポートされる GROUP BY 機能と、データベースの互換性レベルを示します。
特徴量 | SQL Server Integration Services | SQL Server 互換性レベル 100 以上 | SQL Server 2008 以降で互換性レベル 90 |
---|---|---|---|
DISTINCT 集計 | WITH CUBE および WITH ROLLUP ではサポートされていません。 | WITH CUBE、WITH ROLLUP、GROUPING SETS、CUBE、および ROLLUP でサポートされています。 | 互換性レベル 100 と同じです。 |
GROUP BY 句内の、CUBE または ROLLUP の名前を持つユーザー定義関数 | GROUP BY 句では、ユーザー定義関数 dbo.cube(arg1,...argN) または dbo.rollup(arg1, ...argN) を使用できます。 例: SELECT SUM (x) FROM T GROUP BY dbo.cube(y); |
GROUP BY 句では、ユーザー定義関数 dbo.cube (arg1,...argN**)** または dbo.rollup(arg1,...argN) は使用できません。 例: SELECT SUM (x) FROM T GROUP BY dbo.cube(y); "キーワード 'cube'|'rollup' の周辺に正しくない構文があります" というエラー メッセージが返されます。 この問題を回避するには、 dbo.cube を [dbo].[cube] に、または dbo.rollup を [dbo].[rollup] に置き換えます。次の例は使用できます: SELECT SUM (x) FROM T GROUP BY [dbo].[cube](y); |
GROUP BY 句では、ユーザー定義関数 dbo.cube (arg1,...argN) または dbo.rollup(arg1,...argN) を使用できます 例: SELECT SUM (x) FROM T GROUP BY dbo.cube(y); |
GROUPING SETS | サポートされていません | サポートされています | サポートされています |
CUBE | サポートされていません | サポート対象 | サポートされていません |
ROLLUP | サポートされていません | サポート対象 | サポートされていません |
GROUP BY () などの総計 | サポートされていません | サポートされています | サポートされています |
GROUPING_ID 関数 | サポートされていません | サポートされています | サポートされています |
GROUPING 関数 | サポートされています | サポート対象 | サポートされています |
WITH CUBE | サポートされています | サポート対象 | サポートされています |
WITH ROLLUP | サポートされています | サポート対象 | サポートされています |
WITH CUBE または WITH ROLLUP の重複したグループ化の削除 | サポートされています | サポート対象 | サポートされています |
例
A. 単純な GROUP BY 句を使用する
次の例では、SalesOrderID
テーブルから SalesOrderDetail
ごとの合計を取得します。 この例では、AdventureWorks を使用します。
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID;
B. GROUP BY 句を複数のテーブルで使用する
次の例では、City
テーブルと Address
テーブルを結合したものから、EmployeeAddress
ごとに従業員の数を取得します。 この例では、AdventureWorks を使用します。
SELECT a.City, COUNT(bea.AddressID) EmployeeCount
FROM Person.BusinessEntityAddress AS bea
INNER JOIN Person.Address AS a
ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City;
C. GROUP BY 句を式と共に使用する
次の例では、DATEPART
関数を使用して各年の売上合計を取得します。 SELECT
リストと GROUP BY
句の両方に同じ式が存在する必要があります。
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate);
D. GROUP BY 句を HAVING 句と共に使用する
次の例では、HAVING
句で生成されたグループのうち結果セットに含めるグループを、GROUP BY
句を使用して指定します。
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
HAVING DATEPART(yyyy,OrderDate) >= N'2003'
ORDER BY DATEPART(yyyy,OrderDate);
例 :Azure Synapse Analytics と Parallel Data Warehouse
E. GROUP BY 句の基本的な使用方法
次の例では、各日のすべての売上の合計金額を検索します。 各日に、すべての売上の合計を含む 1 つの行が返されます。
-- Uses AdventureWorksDW
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales
GROUP BY OrderDateKey ORDER BY OrderDateKey;
F. DISTRIBUTED_AGG ヒントの基本的な使用方法
この例では、DISTRIBUTED_AGG クエリ ヒントを使用して、集計を実行する前に CustomerKey
列でテーブルをシャッフルするようにアプライアンスに強制します。
-- Uses AdventureWorksDW
SELECT CustomerKey, SUM(SalesAmount) AS sas
FROM FactInternetSales
GROUP BY CustomerKey WITH (DISTRIBUTED_AGG)
ORDER BY CustomerKey DESC;
G. GROUP BY の構文の種類
選択リストに集計がない場合、選択リスト内の各列を GROUP BY リストに含める必要があります。 選択リスト内の計算列は、GROUP BY リストに一覧表示することができますが、必須ではありません。 構文が有効な SELECT ステートメントの例を次に示します。
-- Uses AdventureWorks
SELECT LastName, FirstName FROM DimCustomer GROUP BY LastName, FirstName;
SELECT NumberCarsOwned FROM DimCustomer GROUP BY YearlyIncome, NumberCarsOwned;
SELECT (SalesAmount + TaxAmt + Freight) AS TotalCost FROM FactInternetSales GROUP BY SalesAmount, TaxAmt, Freight;
SELECT SalesAmount, SalesAmount*1.10 SalesTax FROM FactInternetSales GROUP BY SalesAmount;
SELECT SalesAmount FROM FactInternetSales GROUP BY SalesAmount, SalesAmount*1.10;
H. 複数の GROUP BY 式で GROUP BY を使用する
次の例では、複数の GROUP BY
条件を使用して結果をグループ化します。 各 OrderDateKey
グループ内に、DueDateKey
によって区別できるサブグループがある場合、結果セットに新しいグループが定義されます。
-- Uses AdventureWorks
SELECT OrderDateKey, DueDateKey, SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey, DueDateKey
ORDER BY OrderDateKey;
I. GROUP BY 句を HAVING 句と共に使用する
次の例では、HAVING
句を使用して、結果セットに含める必要がある GROUP BY
句で生成されたグループを指定します。 注文日が 2004 年以降のグループのみが結果に含まれます。
-- Uses AdventureWorks
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey
HAVING OrderDateKey > 20040000
ORDER BY OrderDateKey;
参照
GROUPING_ID (Transact-SQL)
GROUPING (Transact-SQL)
SELECT (Transact-SQL)
SELECT 句 (Transact-SQL)