GROUP BY Clause

In a SELECT statement, specifies the columns used to form groups from the rows selected. Each group contains identical values in the specified column(s). In Access, you use the GROUP BY clause to define a totals query. You must also include a GROUP BY clause in a crosstab query in Access. (See TRANSFORM Statement for details.)

Syntax

GROUP BY column-name,...

Notes: A column name in the GROUP BY clause can refer to any column from any table in the FROM clause, even if the column is not named in the select list. If the GROUP BY clause is preceded by a WHERE clause, the database creates the groups from the rows selected after it applies the WHERE clause. When you include a GROUP BY clause in a SELECT statement, the select list must be made up of either SQL aggregate functions or column names specified in the GROUP BY clause.

Example

To find the average and maximum prices for products by category name, enter the following (qxmplCategoryAvgMaxPrice):

SELECT tblProducts.CategoryDescription,
  Avg(tblProducts.UnitPrice) AS AvgOfUnitPrice,
  Max(tblProducts.UnitPrice) AS MaxOfUnitPrice
FROM tblProducts
WHERE tblProducts.TrialVersion = 0
GROUP BY tblProducts.CategoryDescription;