Using the SQL Aggregate Functions

Using the SQL Aggregate Functions
As well as retrieving rows and columns from the database, we often want to perform some
form of summation or aggregation of data, similar to the totals at the bottom of a report.
The ISO standard defines five aggregate functions:
n COUNT – returns the number of values in a specified column;
n SUM – returns the sum of the values in a specified column;
n AVG – returns the average of the values in a specified column;
n MIN – returns the smallest value in a specified column;
n MAX – returns the largest value in a specified column.
These functions operate on a single column of a table and return a single value. COUNT,
MIN, and MAX apply to both numeric and non-numeric fields, but SUM and AVG may
be used on numeric fields only. Apart from COUNT(*), each function eliminates nulls
first and operates only on the remaining non-null values. COUNT(*) is a special use of
COUNT, which counts all the rows of a table, regardless of whether nulls or duplicate
values occur.
If we want to eliminate duplicates before the function is applied, we use the keyword
DISTINCT before the column name in the function. The ISO standard allows the keyword
ALL to be specified if we do not want to eliminate duplicates, although ALL is assumed
if nothing is specified. DISTINCT has no effect with the MIN and MAX functions. However,
it may have an effect on the result of SUM or AVG, so consideration must be given
to whether duplicates should be included or excluded in the computation. In addition,
DISTINCT can be specified only once in a query.
It is important to note that an aggregate function can be used only in the SELECT list
and in the HAVING clause (see Section 5.3.4). It is incorrect to use it elsewhere. If the
SELECT list includes an aggregate function and no GROUP BY clause is being used to
group data together (see Section 5.3.4), then no item in the SELECT list can include any
reference to a column unless that column is the argument to an aggregate function. For
example, the following query is illegal:
SELECT staffNo, COUNT(salary)
FROM Staff;
because the query does not have a GROUP BY clause and the column staffNo in the
SELECT list is used outside an aggregate function.
Example 5.13 Use of COUNT(*)
How many properties cost more than £350 per month to rent?
SELECT COUNT(*) AS myCount
FROM PropertyForRent
WHERE rent > 350;
Restricting the query to properties that cost more than £350 per month is achieved
using the WHERE clause. The total number of properties satisfying this condition can
then be found by applying the aggregate function COUNT. The result table is shown in
Table 5.13.
Example 5.14 Use of COUNT(DISTINCT)
How many different properties were viewed in May 2004?
SELECT COUNT(DISTINCT propertyNo) AS myCount
FROM Viewing
WHERE viewDate BETWEEN ‘1-May-04’ AND ‘31-May-04’;
Again, restricting the query to viewings that occurred in May 2004 is achieved using the
WHERE clause. The total number of viewings satisfying this condition can then be found
by applying the aggregate function COUNT. However, as the same property may be viewed
many times, we have to use the DISTINCT keyword to eliminate duplicate properties. The
result table is shown in Table 5.14.
Example 5.15 Use of COUNT and SUM
Find the total number of Managers and the sum of their salaries.
SELECT COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
WHERE position = ‘Manager’;
Example 5.16 Use of MIN, MAX, AVG
Find the minimum, maximum, and average staff salary.
SELECT MIN(salary) AS myMin, MAX(salary) AS myMax, AVG(salary) AS myAvg
FROM Staff;
In this example we wish to consider all staff and therefore do not require a WHERE clause.
The required values can be calculated using the MIN, MAX, and AVG functions based on
the salary column. The result table is shown in Table 5.16.