GROUP BY (Transact-SQL)

Specifies the groups into which output rows are to be placed. If aggregate functions are included in the SELECT clause <select list>, GROUP BY calculates a summary value for each group. When GROUP BY is specified, either each column in any nonaggregate expression in the select list should be included in the GROUP BY list, or the GROUP BY expression must exactly match the select list expression.

Note

If the ORDER BY clause is not specified, groups returned by using the GROUP BY clause are not in any particular order. To specify a particular ordering of the data, we recommend that you always use the ORDER BY clause.

Transact-SQL Syntax Conventions

Syntax

[ GROUP BY [ ALL ] group_by_expression [ ,...n ] 
    [ WITH { CUBE | ROLLUP } ] 
] 

Arguments

• ALL
  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_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 nonaggregate 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 10 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.

• CUBE
  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. A GROUP BY summary row is displayed as NULL in the result, but is used to indicate all values. 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. Each operand (column) in the GROUP BY clause is bound under the grouping NULL and grouping is applied to all other operands (columns). 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.

• ROLLUP
  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 Microsoft SQL Server 2005 Database Engine returns an error message and cancels the query.

Examples

A. Using a simple GROUP BY clause

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

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

B. Using a GROUP BY clause with multiple tables

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

USE AdventureWorks ;
GO
SELECT a.City, COUNT(ea.AddressID) EmployeeCount
FROM HumanResources.EmployeeAddress ea 
INNER JOIN Person.Address a
ON ea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City ;

C. Using a GROUP BY clause with an expression

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 AdventureWorks ;
GO
SELECT DATEPART(yyyy,OrderDate) AS Year,
SUM(TotalDue) AS AverageOrderAmt
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate) ;

See Also

Reference

SELECT (Transact-SQL)
SELECT Clause (Transact-SQL)

Other Resources

Summarizing Data Using CUBE
Summarizing Data Using ROLLUP

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	New content: Added example C.
5 December 2005	Changed content: Fixed example B.