GROUP BY (Transact-SQL)

• ALL
  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Includes all groups and result sets, even those that do not have any rows that meet the search condition specified in the WHERE clause. When ALL is specified, null values are returned for the summary columns of groups that do not meet the search condition. You cannot specify ALL with the CUBE or ROLLUP operators.

  GROUP BY ALL is not supported in queries that access remote tables if there is also a WHERE clause in the query. GROUP BY ALL will fail on columns that have the FILESTREAM attribute.

• group_by_expression
  Is an expression on which grouping is performed. group_by_expression is also known as a grouping column. group_by expression can be a column or a non-aggregate expression that references a column returned by the FROM clause. A column alias that is defined in the SELECT list cannot be used to specify a grouping column.

  Note

  Columns of type text, ntext, and image cannot be used in group_by_expression.

  For GROUP BY clauses that do not contain CUBE or ROLLUP, the number of group_by_expression items is limited by the GROUP BY column sizes, the aggregated columns, and the aggregate values involved in the query. This limit originates from the limit of 8,060 bytes on the intermediate worktable that is needed to hold intermediate query results. A maximum of 12 grouping expressions is permitted when CUBE or ROLLUP is specified.

  xml data type methods cannot be specified directly in group_by_expression. Instead, refer to a user-defined function that uses xml data type methods inside it, or refer to a computed column that uses them.

• WITH CUBE
  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. A GROUP BY summary row is returned for every possible combination of group and subgroup in the result set. Use the GROUPING function to determine whether null values in the result set are GROUP BY summary values.

  The number of summary rows in the result set is determined by the number of columns included in the GROUP BY clause. Because CUBE returns every possible combination of group and subgroup, the number of rows is the same, regardless of the order in which the grouping columns are specified.

• WITH ROLLUP
  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a hierarchical order, from the lowest level in the group to the highest. The group hierarchy is determined by the order in which the grouping columns are specified. Changing the order of the grouping columns can affect the number of rows produced in the result set.

  Important

  Distinct aggregates, for example, AVG (DISTINCT column_name), COUNT (DISTINCT column_name), and SUM (DISTINCT column_name), are not supported when you use CUBE or ROLLUP. If these are used, the SQL Server Database Engine returns an error message and cancels the query.

Syntax Limitations

Size Limitations

In SQL Server 2008 and higher versions, the GROUP BY clause cannot contain a subquery in an expression that is used for the group by list. Error 144 is returned.

SQL Server 2008 and higher versions support all GROUP BY features that are included in the SQL-2006 standard with the following syntax exceptions:

• Grouping sets are not allowed in the GROUP BY clause unless they are part of an explicit GROUPING SETS list. For example, GROUP BY Column1, (Column2, ...ColumnN) is allowed in the standard but not in SQL Server. GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) or GROUP BY Column1, Column2, ... ColumnN is allowed. These are semantically equivalent to the previous GROUP BY example. This is to avoid the possibility that GROUP BY Column1, (Column2, ...ColumnN) might be misinterpreted as GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)). This is not semantically equivalent.

• Grouping sets are not allowed inside grouping sets. For example, GROUP BY GROUPING SETS (A1, A2,…An, GROUPING SETS (C1, C2, ...Cn)) is allowed in the SQL-2006 standard but not in SQL Server. SQL Server 2008 and higher versions allow GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) or GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ). These examples are semantically equivalent to the first GROUP BY example and have a clearer syntax.

• GROUP BY [ALL/DISTINCT] is not allowed in a general GROUP BY clause or with the GROUPING SETS, ROLLUP, CUBE, WITH CUBE or WITH ROLLUP constructs. ALL is the default and is implicit.

The following table describes the GROUP BY features that are supported based upon the version of SQL Server and the database compatibility level.

SELECT SUM (x)
FROM T 
GROUP BY dbo.cube(y); 

SELECT SUM (x)
FROM T 
GROUP BY dbo.cube(y); 

SELECT SUM (x)
FROM T 
GROUP BY [dbo].[cube](y);

SELECT SUM (x)
FROM T 
GROUP BY dbo.cube(y);

The following example retrieves the total for each SalesOrderID from the SalesOrderDetail table.

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

The following example retrieves the number of employees for each City from the Address table joined to the EmployeeAddress table.

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;

The following example retrieves the total sales for each year by using the DATEPART function. The same expression must be present in both the SELECT list and GROUP BY clause.

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);

The following example uses the HAVING clause to specify which of the groups generated in the GROUP BY clause should be included in the result set.

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);