Groups and aggregate functions

You can produce summary statistics for a table using functions on fields with the SQL statement.

For example, using the Employee table:

SELECT COUNT(Department), SUM(Age), MIN(JoinDate), MAX(JoinDate) FROM Employee;

produces a single row of summary information:

COUNT(Department)

SUM(Age)

MIN(JoinDate)

MAX(JoinDate)

5

147

1-Oct-1986

10-Mar-1992

You can use these functions in conjunction with the GROUP BY keywords to produce consolidated group statistics. For example:

SELECT Department, AVG(Age), COUNT(*) FROM Employee GROUP BY Department;

This time we can include Department in the select statement as this is the field we are grouping on. The results table lists, in each department, the average age and the total number of people.

Department

AVG(Age)

COUNT(*)

ADMIN

30

1

SALES

25

2

TECH

33.5

2

As normal select statements have an optional WHERE clause to specify which rows are included from the table, so GROUP statements have an optional HAVING clause.

For example, to show a list of departments with an average age over 30, use the following:

SELECT Department, AVG(Age), COUNT(*) FROM Employee GROUP BY Department HAVING AVG(Age) > 30;

Here’s a complete list of aggregate functions you can use:

Function

Meaning

COUNT

Count the number of records

SUM

Total

MAX

Find the maximum value of field

MIN

Find the minimum value of field

AVG

Average

VAR

Sample variance

VARP

Population variance

STDEV

Sample standard deviation

STDEVP

Population standard deviation

See also:

Grouping on more than one level

Finding duplicate records

Other types of SQL statements