The GROUP BY Clause

This is used for grouping and aggregating data. Every field listed in the SELECT clause must either appear in the GROUP BY clause or have a aggregation operation applied to it.

The syntax for the GROUP BY clause is:

GROUP BY field-list

where "field-list" is a comma-separated list of the fields in the SELECT clause that do not have a aggregation operation applied.

Aggregation operations are applied to all values of the field for a sub-set of records. The sub-set of records is defined by the unique set of values from the fields in the GROUP BY clause.

Example:

Given a table with the following records:

CourseName	StudentName	ExamNumber	ExamGrade
Computer Programming	John Jones	1	85
Computer Programming	John Jones	2	79
Computer Programming	Jane Smith	1	82
Computer Programming	Jane Smith	2	91

Creating a query that calculates the exam average by course and student would result in:

CourseName	StudentName	AvgOfExamGrade
Computer Programming	John Jones	82
Computer Programming	Jane Smith	86.5

Creating a query that calculates exam max by course and student would result in:

CourseName	StudentName	MaxOfExamGrade
Computer Programming	John Jones	85
Computer Programming	Jane Smith	91

Creating a query that calculates exam first by course and student would result in:

CourseName	StudentName	FirstOfExamGrade
Computer Programming	John Jones	85
Computer Programming	Jane Smith	82

The aggregation operations for a field are:

SUM	Adds together the values
AVG	Takes the average (mean) of the values
MAX	Returns the highest value
MIN	Returns the lowest value
COUNT	Returns the count of records
STDEV	Returns the standard deviation of the values
VAR	Returns the variance of the values
FIRST	Returns the first value
LAST	Returns the last value