Aggregate functions and GROUP BY

Aggregate (multiple row) functions work on a group of rows, and return one result for the whole group: MAX, MIN, AVG, SUM and COUNT.

Three forms of COUNT:

• COUNT(∗) counts all the rows;
• COUNT(column) counts the values in that column, ignoring NULL;
• COUNT(DISTINCT column) counts the different values.

(In real SQL, the star inside COUNT is the ordinary asterisk key.)

GROUP BY puts rows with equal values in a column into groups, so an aggregate function can be applied to each group: for example, the number of cars bought by each customer, or the total paid in each payment mode.

HAVING puts a condition on the groups. For example, to list customers who bought more than one car: GROUP BY CustID HAVING COUNT(∗) > 1.

Remember the difference: WHERE filters rows before grouping; HAVING filters groups after grouping. (Note: MySQL has no function called Average(); it is AVG.)