Export (0) Print
Expand All
Expand Minimize

GROUP BY (Transact-SQL)

Updated: 17 July 2006

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.

ms177673.note(en-US,SQL.90).gifNote:
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.

Topic link icon Transact-SQL Syntax Conventions


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

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.

ms177673.note(en-US,SQL.90).gifNote:
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.

ms177673.note(en-US,SQL.90).gifImportant:
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.

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

Release History

17 July 2006

New content:
  • Added example C.

5 December 2005

Changed content:
  • Fixed example B.

Community Additions

ADD
Show:
© 2014 Microsoft