Obtaining aggregate data

This section tells how to construct queries that give you aggregate information. Examples of aggregate information are:

• The total of all values in a column

• The number of entries in a column

• The average value of entries in a column

Suppose you want to know how many employees are in the database. The following statement retrieves the number of rows in the employee table:

SELECT count( * )
FROM Employees

The result returned from this query is a table with only one column (with title count(*)) and one row, which contains the number of employees.

The following command is a slightly more complicated aggregate query:

SELECT   count( * ), 
min( BirthDate ),
max( BirthDate )
FROM Employees

The result set from this query has three columns and only one row. The three columns contain the number of employees, the birth date of the oldest employee, and the birth date of the youngest employee.

COUNT, MIN, and MAX are called aggregate functions. Aggregate functions summarize entire tables from the database using the GROUP BY clause of the SELECT statement. In total, there are seven aggregate functions: AVG, COUNT, MAX, MIN, STDDEV, SUM, and VARIANCE. All of the functions have either the name of a column or an expression as a parameter. As you have seen, COUNT also has an asterisk as its parameter, which returns the number of rows in each group.