GROUP BY Fundamentals

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.

In addition, the HAVING clause is typically used in conjunction 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 Northwind.dbo.Employees

GROUP BY DATEPART(yy, HireDate)

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 earlier 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 type and publisher ID:

USE pubs

SELECT type, pub_id, 'avg' = AVG(price), 'sum' = sum(ytd_sales)

FROM titles

GROUP BY type, pub_id

Here is the result set:

type         pub_id avg                    sum        

------------ ------ ---------------------- -----------

business     0736   11.96                  18722      

business     1389   17.31                  12066      

mod_cook     0877   11.49                  24278      

popular_comp 1389   21.48                  12875      

psychology   0736   45.93                  9564       

psychology   0877   21.59                  375        

trad_cook    0877   15.96                  19566      

UNDECIDED    0877   (null)                 (null)     

(8 row(s) affected)

See Also

SELECT