GROUP BY Clause - Access - Microsoft Office Online

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

GROUP BY Clause

Applies to: Microsoft Office Access 2007

Combines records with identical values in the specified field list into a single record. A summary value is created for each record if you include an SQL aggregate function , such as Sum or Count, in the SELECT statement.

Syntax

SELECT fieldlist
FROM table
WHERE criteria
[GROUP BY groupfieldlist]

A SELECT statement containing a GROUP BY clause has these parts:

Part	Description
fieldlist	The name of the field or fields to be retrieved along with any field-name aliases (alias (SQL): An alternative name for a table or field in expressions. Often used to shorten the table or field name for subsequent references in code, to prevent possible ambiguous references, or to provide a more descriptive name in query output.), SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table	The name of the table from which records are retrieved.
criteria	Selection criteria. If the statement includes a WHERE clause, the Microsoft Access database engine groups values after applying the WHERE conditions to the records.
groupfieldlist	The names of up to 10 fields used to group records. The order of the field names in groupfieldlist determines the grouping levels from the highest to the lowest level of grouping.

Remarks

GROUP BY is optional.

Summary values are omitted if there is no SQL aggregate function in the SELECT statement.

Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) values in GROUP BY fields are grouped and are not omitted. However, Null values are not evaluated in any SQL aggregate function.

Use the WHERE clause to exclude rows you do not want grouped, and use the HAVING clause to filter records after they have been grouped.

Unless it contains Memo (Memo data type: In a Microsoft Access database, this is a field data type. Memo fields can contain up to 65,535 characters.) or OLE Object (OLE Object data type: A field data type that you use for objects created in other applications that can be linked or embedded (inserted) in an Access database.) data, a field in the GROUP BY field list can refer to any field in any table listed in the FROM clause, even if the field is not included in the SELECT statement, provided the SELECT statement includes at least one SQL aggregate function. The Microsoft Access database engine cannot group on Memo or OLE Object fields.

All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.