Aggregate Values and GROUP BY

Aggregate values can be calculated at levels other than for the entire table. The SQL GROUP BY clause is used to create one output record per each group of unique values, and optionally, to produce summary values for selected fields. GROUP BY used alone creates an output record that specifies the unique values:

SELECT 
	Products.CategoryID, 
	Products.SupplierID
FROM Products
GROUP BY 
	Products.CategoryID, 
	Products.SupplierID;

The previous query lists the unique combinations of CategoryID and SupplierID, and produces the same results as:

SELECT DISTINCT 
	Products.CategoryID, 
	Products.SupplierID
FROM Products;

As in other SQL dialects, any output fields in a GROUP BY query must be either part of an aggregate function or be in the GROUP BY clause.

When an aggregate function is combined with a GROUP BY clause, one record of output is produced for each unique value in the GROUP BY clause. The aggregate totals are accumulated and output for each record, and then reset for the next group, as in this example, which finds the oldest birth date for each group of employees that have the same title:

SELECT 
	Employees.Title, 
	Min(Employees.BirthDate) AS MinOfBirthDate
FROM Employees
GROUP BY Employees.Title;

You can also sort the output of the GROUP BY query by adding an ORDER BY clause, as in this example, which finds the most recently hired employee in each country:

SELECT 
	Employees.Country, 
	Max(Employees.HireDate) AS MaxOfHireDate
FROM Employees
GROUP BY Employees.Country
ORDER BY Max(Employees.HireDate) DESC;