Example: Aggregate Functions

All of the aggregate functions are applied only to the set of records retrieved by the SELECT command. Examples of each of the functions are given below.

To find the number of "CAM" parts in the table, enter:

SELECT COUNT(pno) FROM part
WHERE pname = "CAM"

The result is 2. To find the total quantity of P2 supplied, enter:

SELECT SUM(qty) FROM partsupp
WHERE pno = "P2"

The result is 1000. To find the weight of the heaviest part, enter:

SELECT MAX(weight) FROM part

More than one function can be used in a command, as in the query:

SELECT COUNT(pno), AVG(weight), MIN(weight),
  MAX(weight), XSTDDEV(weight)
FROM part

Commands containing both an aggregate function and a simple field-name are not allowed unless the result is grouped by the simple field. For example, the following query is illegal:

SELECT SUM(qty), pno
FROM partsupp

To correct it, add a GROUP BY clause, as shown below:

SELECT SUM(qty), pno
  FROM partsupp
  GROUP BY pno

The next example finds the average weight, and number of parts for every part color that has two or more parts, and displays them in color order.

SELECT color, AVG(weight), COUNT(*)
  FROM part
  GROUP BY color
    HAVING COUNT(*) >= 2
  ORDER BY color

The result is: