Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
GROUP BY Components

GROUP BY Components

SQL Server 2005

The GROUP BY clause contains the following components:

  • One or more aggregate-free expressions. These are usually references to the grouping columns.
  • Optionally, the ALL keyword, which specifies that all groups produced by the GROUP BY clause are returned, even if some of the groups do not have any rows that meet the search conditions.
  • CUBE or ROLLUP.
  • Typically, the HAVING clause is used with the GROUP BY clause, although HAVING can be specified separately.

You can group by an expression as long as it does not include aggregate functions. For example:

SELECT DATEPART(yy, HireDate) AS Year,
       COUNT(*) AS NumberOfHires
FROM AdventureWorks.HumanResources.Employee
GROUP BY DATEPART(yy, HireDate)

Here is the result set.

Year        NumberOfHires
----------- -------------
1997        2
2001        21
2000        45
1996        1
2003        3
1999        198
1998        16
2002        4

(8 row(s) affected)

In a GROUP BY, you must specify the name of a table or view column, not the name of a result set column assigned with an AS clause. For example, replacing the GROUP BY DATEPART(yy, HireDate) clause with GROUP BY Year is not legal.

You can list more than one column in the GROUP BY clause to nest groups; that is, you can group a table by any combination of columns. For example, this query finds the average price and the sum of year-to-date sales, grouped by product ID and special offer ID:

USE AdventureWorks;
GO
SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS 'Average Price', 
    SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID
ORDER BY ProductID
GO

Community Additions

ADD
Show:
© 2015 Microsoft