The GROUP BY clause

GROUP BY is an optional clause that 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() or AVG(), in the SELECT statement.

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

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. Use the HAVING clause to filter records after they have been grouped, but you do not want them to appear in the recordset.

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.

For example, the following SQL statement returns the total of units in stock for each product:

SELECT ProductName, Sum(UnitsInStock) as "Total Units in Stock"
FROM Products
GROUP BY ProductName

If you want only the units in stock for a given product, say a product named "ESCARPANDOLA", you can use the WHERE clause to filter the records used by the query:

SELECT ProductName, Sum(UnitsInStock) as "Total Units in Stock"
FROM Products
WHERE ProductName = "ESCARPANDOLA"
GROUP BY ProductName