Getting summarizing values

How many PC models does a particular supplier produce? How the average price is defined for computers with the same specifications? The answers to these and other questions associated with some statistic information may be obtained by means of summarizing (aggregate) functions. The following aggregate functions are assumed as standard:

All these functions return a single value. In so doing, the functions COUNT, MIN, and MAX are applicable to any data types, while the functions SUM and AVG are only used with numeric data types. The difference between the functions COUNT(*) and COUNT(<column name>) is that the second does not calculate NULL values (as do other aggregate functions).

Example 5.5.1

SELECT MIN(price) AS Min_price, MAX(price) AS Max_price
FROM PC;

The result is a single row containing the aggregate values:

Example 5.5.2

SELECT COUNT(*) AS Qty
FROM PC
WHERE model IN(SELECT model
FROM Product
WHERE maker = 'A'
);

As a result we get:

Example 5.5.3

SELECT COUNT(model) AS Qty_model
FROM Product
WHERE maker = 'A';

Example 5.5.4

This query is similar to the preceding one for the total number of models produced by maker A. Now we need to find the number of different models in the PC table (available for sale).

To use only unique values in calculating the statistic, the parameter DISTINCT with an aggregate function argument may be used. ALL is another (default) parameter and assumes that all the column values returned (besides NULLs) are calculated. The statement

SELECT COUNT(DISTINCT model) AS Qty
FROM PC
WHERE model IN (SELECT model
FROM Product
WHERE maker = 'A'
);

gives the following result:

If we need the number of PC models produced by each maker, we will need to use the GROUP BY clause, placed immediately after the WHERE clause, if any.

Suggested exercises: 10, 11, 12, 13, 18, 24, 25, 26, 27, 40, 41, 43, 51, 53, 54, 58, 61, 62, 75, 77, 79, 80, 81, 85, 86, 88, 91, 92, 93, 94, 95, 96, 103, 109, 127, 129