GROUP BY (Transact-SQL)

SQL Server 2008 R2 では、選択した行セットを、1 つ以上の列または式の値ごとに、集計行セットにグループ化します。グループごとに 1 つの行が返されます。SELECT 句の <select> リストに含まれる集計関数は、個別の行ではなく各グループに関する情報を提供します。

GROUP BY 句には、ISO に準拠する構文と ISO に準拠しない構文があります。1 つの SELECT ステートメント内で使用できるのは、1 つの構文スタイルだけです。新規の作業では、ISO に準拠する構文を使用してください。ISO に準拠しない構文は、旧バージョンとの互換性を維持するために提供されています。

このトピックでは、一般的な GROUP BY 句と単純な GROUP BY 句について説明します。

• 一般的な GROUP BY 句には、GROUPING SETS、CUBE、ROLLUP、WITH CUBE、または WITH ROLLUP が含まれます。

• 単純な GROUP BY 句には、GROUPING SETS、CUBE、ROLLUP、WITH CUBE、または WITH ROLLUP は含まれません。総計の GROUP BY () は、単純な GROUP BY と見なされます。

Transact-SQL 構文表記規則 (Transact-SQL)

構文

        ISO-Compliant Syntax

GROUP BY <group by spec>

<group by spec> ::=
    <group by item> [ ,...n ]

<group by item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>
    | <grouping sets spec>
    | <grand total>

<simple group by item> ::=
    <column_expression>

<rollup spec> ::=
    ROLLUP ( <composite element list> )<cube spec> ::=
    CUBE ( <composite element list> )<composite element list> ::=
    <composite element> [ ,...n ]

<composite element> ::=
    <simple group by item>
    | ( <simple group by item list> )<simple group by item list> ::=
    <simple group by item> [ ,...n ]

<grouping sets spec> ::=
    GROUPING SETS ( <grouping set list> )<grouping set list> ::=
    <grouping set> [ ,...n ]

<grouping set> ::=
    <grand total>
    | <grouping set item>
    | ( <grouping set item list> )<empty group> ::=()<grouping set item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>

<grouping set item list> ::=
    <grouping set item> [ ,...n ]

        Non-ISO-Compliant Syntax
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
    [ WITH { CUBE | ROLLUP } ] 
]

引数

• <column_expression>
  グループ化操作を実行する対象となる式です。

• ROLLUP ( )
  単純な GROUP BY 集計行に加えて、小計行または特殊集計行、および総計行も生成します。

  返されるグループの数は、<composite element list> 内の式の数に 1 を加算した数と同じです。たとえば、次のステートメントについて考えてみます。

  SELECT a, b, c, SUM ( <expression> )
  FROM T
  GROUP BY ROLLUP (a,b,c);
  

  (a, b, c)、(a, b)、および (a) の値の一意の組み合わせごとに、小計が 1 行ずつ生成されます。総計行も計算されます。

  列は右から左にロールアップされます。列の順序は ROLLUP の出力グループに影響を及ぼし、結果セット内の行数にも影響する場合があります。

• CUBE ( )
  単純な GROUP BY 集計行、ROLLUP 特殊集計行、およびクロス集計行を生成します。

  CUBE は、<composite element list> 内の式のすべての順列のグループを出力します。

  生成されるグループの数は、<composite element list> 内の式の数を n としたとき、(2n) です。たとえば、次のステートメントについて考えてみます。

  SELECT a, b, c, SUM (<expression>)
  FROM T
  GROUP BY CUBE (a,b,c);
  

  (a, b, c)、(a, b)、(a, c)、(b, c)、(a)、(b)、および (c) の値の一意の組み合わせごとに 1 行ずつ生成され、各行の小計と総計行が生成されます。

  列の順序は、CUBE の出力に影響しません。

• GROUPING SETS ( )
  1 つのクエリでデータの複数のグループを指定します。CUBE や ROLLUP で生成される完全な集計セットではなく、指定したグループだけが集計されます。結果は、指定したグループの UNION ALL と同じになります。GROUPING SETS には、1 つの要素または要素のリストを含めることができます。GROUPING SETS で指定できるグループは、ROLLUP または CUBE から返されるグループと同じです。例については、「GROUPING SETS の対応」を参照してください。<grouping set item list> には、ROLLUP または CUBE を含めることができます。

• ( )
  空のグループを指定すると、総計が生成されます。

ISO に準拠しない構文

• ALL
  この機能は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では、この機能の使用を避け、現在この機能を使用しているアプリケーションは修正するようにしてください。 すべてのグループおよび結果セットを含みます。これには、WHERE 句に指定された検索条件を満たす行がまったくないグループも含まれます。ALL を指定した場合、検索条件を満たさないグループの集計列には NULL 値が返されます。CUBE または ROLLUP 演算子を使用している場合は、ALL を指定できません。

  クエリ内に WHERE 句がある場合、リモート テーブルにアクセスするクエリでは、GROUP BY ALL はサポートされません。FILESTREAM 属性を持つ列では、GROUP BY ALL が失敗します。

• group_by_expression
  グループ化を実行するときの式です。group_by_expression はグループ化列とも呼ばれます。group_by expression には列を指定するか、FROM 句で返される列を参照する非集計式を指定できます。SELECT リスト内に定義されている列の別名は、グループ化列の指定には使用できません。

  注
  group_by_expression では、text、ntext、および image 型の列は使用できません。

  CUBE または ROLLUP を含まない GROUP BY 句では、group_by_expression 項目の数はクエリにかかわる GROUP BY 列のサイズ、集計列、および集計値によって制限されます。この数制限は、クエリの中間結果を保持するために中間作業テーブル上に必要な 8,060 バイトを基本にしています。CUBE または ROLLUP を指定している場合は、最大 12 個のグループ化式を使用できます。

  xml データ型のメソッドを group_by_expression の中で直接指定することはできません。代わりに、内部で xml データ型のメソッドを使用するユーザー定義関数か、それを使用する計算列を参照します。

• WITH CUBE
  この機能は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では、この機能の使用を避け、現在この機能を使用しているアプリケーションは修正するようにしてください。GROUP BY 句によって提供される通常の行の他に、集計行を結果セットに含むことを指定します。結果セット内のあらゆるグループおよびサブグループの組み合わせに対し GROUP BY 集計行を返します。結果セット内の NULL 値が GROUP BY 集計値であるかどうかを判断するには、GROUPING 関数を使用します。

  結果セットの集計行の数は、GROUP BY 句に含まれる列数により決まります。CUBE ではあらゆるグループとサブグループの組み合わせを返すため、グループ化列の指定順にかかわらず、行の数は同じになります。

• WITH ROLLUP
  この機能は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では、この機能の使用を避け、現在この機能を使用しているアプリケーションは修正するようにしてください。GROUP BY 句によって提供される通常の行の他に、集計行を結果セットに含むことを指定します。グループは、グループ内の最も低いレベルから最も高いレベルへと、階層順にまとめられます。グループの階層は、グループ化列の指定順で決まります。グループ化列の順序を変更すると、結果セット内に生成される行の数が変わる場合があります。

  重要
  AVG (DISTINCT column_name)、COUNT (DISTINCT column_name)、および SUM (DISTINCT column_name) など非重複値の集計は、CUBE または ROLLUP を使用している場合はサポートされません。これらの関数が使用されると、SQL Server データベース エンジンはエラー メッセージを返し、クエリを取り消します。

説明

GROUP BY 句の式には、FROM 句のテーブルの列、派生テーブル、またはビューを含めることができます。列が SELECT 句の <select list> に示されている必要はありません。

<select list> 内にある非集計式のテーブル列やビュー列は、それぞれ 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 句の <select list> に集計関数が含まれている場合は、GROUP BY によって各グループの集計値が計算されます。これらはベクター集計値と呼ばれます。

WHERE 句の条件と一致しない行は、グループ化操作の実行前に削除されます。

HAVING 句を GROUP BY 句と組み合わせて使用すると、結果セット内のグループをフィルターできます。

GROUP BY 句では、結果セットの並べ替えが行われません。結果セットを並べ替えるには、ORDER BY 句を使用します。

グループ列に NULL 値が含まれている場合、すべての NULL 値は等しいものと見なされ、1 つのグループに配置されます。

AS 句で列名を置換する別名を GROUP BY で使用するためには、FROM 句の派生テーブルで列名を別名に置換しておく必要があります。

GROUPING SETS リスト内の重複したグループ化セットは削除されません。重複したグループ化セットは、列の式を複数回指定した場合や、CUBE または ROLLUP で生成される列の式を GROUPING SETS リストに表示した場合に生成されることがあります。

AVG (DISTINCT column_name)、COUNT (DISTINCT column_name)、および SUM (DISTINCT column_name) など非重複値の集計は、ROLLUP、CUBE、および GROUPING SETS でサポートされています。

ROLLUP、CUBE、および GROUPING SETS をインデックス付きビューで指定することはできません。

GROUP BY または HAVING を ntext、text、または image の列で直接使用することはできません。これらの列は、SUBSTRING() や CAST() など、別のデータ型の値を返す関数の引数として使用できます。

xml データ型のメソッドを <column_expression> の中で直接指定することはできません。代わりに、内部で xml データ型のメソッドを使用するユーザー定義関数か、それを使用する計算列を参照します。

GROUPING SETS、ROLLUP、および CUBE に関する GROUP BY の制限事項

構文の制限

GROUP BY 句では、GROUPING SETS リストに含まれていないグループ化セットは使用できません。たとえば、GROUP BY C1, (C2,..., Cn) は使用できませんが、GROUP BY GROUPING SETS (C1, (C2, ..., Cn)) は使用できます。

GROUPING SETS 内で GROUPING SETS を使用することはできません。たとえば、GROUP BY GROUPING SETS (C1, GROUPING SETS (C2, C3)) は使用できません。

ROLLUP、CUBE、または GROUPING SETS のキーワードを持つ GROUP BY 句では、ISO に準拠しない ALL、WITH CUBE、WITH ROLLUP の各キーワードを使用できません。

サイズの制限

単純な GROUP BY の場合、式の数に制限はありません。

ROLLUP、CUBE、または GROUPING SETS を使用する GROUP BY 句の場合、式の最大数は 32、生成できるグループ化セットの最大数は 4096 (212) です。次の例は、GROUP BY 句が複雑すぎるために失敗します。

• 次の例では、8192 (213) 個のグループ化セットが生成されます。

  GROUP BY CUBE (a1, ..., a13) 
  GROUP BY a1, ..., a13 WITH CUBE 
  

• 次の例では、4097 (212 + 1) 個のグループ化セットが生成されます。

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )
  

• 次の例でも、4097 (212 + 1) 個のグループ化セットが生成されます。CUBE () と () の両方のグループ化セットで総計行が生成され、重複したグループ化セットは削除されません。

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())
  

ISO および ANSI SQL-2006 の GROUP BY 機能のサポート

SQL Server 2008 以降のバージョンの GROUP BY 句では、GROUP BY リストに使用される式にはサブクエリを含めることができません。エラー 144 が返されます。

SQL Server 2008 以降のバージョンでは、SQL-2006 標準規格に含まれているすべての GROUP BY 機能をサポートしています。ただし、次のような構文上の例外があります。

• GROUP BY 句では、明示的な GROUPING SETS リストに含まれていないグループ化セットは使用できません。たとえば、GROUP BY Column1, (Column2, ...ColumnN) は標準規格で使用できますが、SQL Server では使用できません。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 標準規格で使用できますが、SQL Server では使用できません。SQL Server 2008 以降のバージョンでは、GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) や GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ) を使用できます。これらの例は、最初の GROUP BY の例と意味が同等で、構文がより明確です。

• 一般的な GROUP BY 句、または GROUPING SETS、ROLLUP、CUBE、WITH CUBE、WITH ROLLUP の各構造では、GROUP BY [ALL/DISTINCT] を使用できません。ALL は既定値であり暗黙的です。

サポートされる GROUP BY 機能の比較

次の表に、SQL Server のバージョンに基づいてサポートされる GROUP BY 機能と、データベースの互換性レベルを示します。

機能	SQL Server 2005 Integration Services	SQL Server 2008 互換性レベル 100	SQL Server 2008 互換性レベル 90 以下
DISTINCT 集計	WITH CUBE および WITH ROLLUP ではサポートされていません。	WITH CUBE、WITH ROLLUP、GROUPING SETS、CUBE、および ROLLUP でサポートされています。	SQL Server 2008 互換性レベル 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 の重複したグループ化の削除	サポートされています。	サポートされています。	サポートされています。

例

GROUPING SETS、ROLLUP、および CUBE を使用する例については、「ROLLUP、CUBE、および GROUPING SETS を指定した GROUP BY の使用」を参照してください。

A. 単純な GROUP BY 句を使用する

次の例では、SalesOrderDetail テーブルから SalesOrderID ごとの合計を取得します。

USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID;

B. GROUP BY 句を複数のテーブルで使用する

次の例では、Address テーブルと EmployeeAddress テーブルを結合したものから、City ごとに従業員の数を取得します。

USE AdventureWorks2008R2;
GO
SELECT a.City, COUNT(bea.AddressID) AS 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 句の両方に同じ式が存在する必要があります。

USE AdventureWorks2008R2;
GO
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 句と共に使用する

次の例では、GROUP BY 句で生成されたグループのうち結果セットに含めるグループを、HAVING 句を使用して指定します。

USE AdventureWorks2008R2;
GO
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);

関連項目

参照

GROUPING_ID (Transact-SQL)

GROUPING (Transact-SQL)

SELECT (Transact-SQL)

SELECT 句 (Transact-SQL)

概念

ROLLUP、CUBE、および GROUPING SETS を指定した GROUP BY の使用

GROUPING SETS の対応

GROUP BY エラーのトラブルシューティング