GROUP BY Clause

Description

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, 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. For more information, see the FROM clause.
criteria	Selection criteria. If the statement includes a WHERE clause, the Microsoft Jet 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 values in GROUP BY fields are grouped and aren't omitted. However, Null values aren't evaluated in any SQL aggregate function.

Use the WHERE clause to exclude rows you don't want grouped, and use the HAVING clause to filter records after they've been grouped.

Unless it contains Memo or OLE Object 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 isn't included in the SELECT statement, provided the SELECT statement includes at least one SQL aggregate function. The Microsoft Jet database engine can't 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.

See Also ALL, DISTINCT, DISTINCTROW, TOP predicates, FROM clause, HAVING clause, ORDER BY clause, SELECT statement, SELECT...INTO statement, WHERE clause.

Specifics (Microsoft Access)

Including a GROUP BY clause in an SQL statement is equivalent to creating a totals query in the Query window and setting the Total cell to Group By for the appropriate field.

Example

This example creates a list of unique job titles and the number of employees with each title.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub GroupByX1()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' For each title, count the number of employees 
    ' with that title. 
    Set rst = dbs.OpenRecordset("SELECT Title, " _
        & "Count([Title]) AS Tally " _
        & "FROM Employees GROUP BY Title;")
    
    ' Populate the Recordset.
    rst.MoveLast
    
    ' Call EnumFields to print the contents of the 
    ' Recordset. Pass the Recordset object and desired
    ' field width.
    EnumFields rst, 25

    dbs.Close

End Sub

For each unique job title, this example calculates the number of employees in Washington who have that title.

Sub GroupByX2()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' For each title, count the number of employees 
    ' with that title. Only include employees in the 
    ' Washington region.
    Set rst = dbs.OpenRecordset("SELECT Title, " _
        & "Count(Title) AS Tally " _
        & "FROM Employees WHERE Region = 'WA' " _
        & "GROUP BY Title;")

    ' Populate the Recordset.
    rst.MoveLast

    ' Call EnumFields to print the contents of the 
    ' Recordset. Pass the Recordset object and desired
    ' field width.
    EnumFields rst, 25

    dbs.Close

End Sub

Example (Microsoft Access)

To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

The following example finds the average of the unit price for all products from each supplier and groups the list of prices by supplier:

SELECT SupplierID, Avg(UnitPrice) AS AvgOfUnitPrice
FROM Products GROUP BY SupplierID;

The next example finds the maximum unit price for a product in each category:

SELECT CategoryID, Max(UnitPrice) AS MaxOfUnitPrice
FROM Products GROUP BY CategoryID;

The following example counts the number of orders attributed to each employee in the database:

SELECT EmployeeID, Count(OrderID) AS CountOfOrderID
FROM Orders GROUP BY EmployeeID;