Aggregate Functions - Database Design Manual: Using MySQL for Windows - page 112

Figure 10.12 Columns mixed with aggregates must use GROUP BY.

AVG

Although we calculated the average manually in the previous section, it is such a useful tool
that there is a built-in function in MySQL. The AVG function calculates the average value of
a set of numeric values. So to find the average that our visitors gave our website we would
use:

SELECT AVG(score)
FROM visitorbook

This query will give similar results to those shown in Figure 10.10, although to a higher
precision than the manual calculation.

HAVING

When using the WHERE clause in an aggregate query, we have already mentioned that it
has to be placed before the GROUP BY clause as it restricts the query before it has been
sorted into groups. The HAVING clause is placed after the GROUP BY clause, and allows
you to restrict after the group and by an aggregate. Examine the following query:

SELECT familyname, AVG(score) AS goodaverage
FROM visitorbook
GROUP BY familyname
HAVING AVG(score) >= 8

Similar to the previous query, this one calculates and displays the average score for each
family, but then restricts the results, using the HAVING clause, to groups whose average is