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.
|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.|
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.
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.
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.
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.
A. Using a simple GROUP BY clause
The following example retrieves the total for each
SalesOrderID from the
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
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) ;