MySQL in a Nutshell, 2nd Edition

Name

AVG()

Synopsis

AVG([DISTINCT] column)

This function returns the average or mean of a set of numbers given as the argument. It returns NULL if unsuccessful. The DISTINCT keyword causes the function to count only unique values in the calculation; duplicate values will not factor into the averaging.

When returning multiple rows, you generally want to use this function with the GROUP BY clause that groups the values for each unique item, so that you can get the average for that item. This will be clearer with an example:

SELECT sales_rep_id,
CONCAT(name_first, SPACE(1), name_last) AS rep_name,
AVG(sale_amount) AS avg_sales
FROM sales
JOIN sales_reps USING(sales_rep_id)
GROUP BY sales_rep_id;

This SQL statement returns the average amount of sales in the sales table made by each sales representative. It will total all values found for the sale_amount column, for each unique value for sales_rep_id, and divide by the number of rows found for each of those unique values. If you would like to include sales representatives who made no sales in the results, you’ll need to change the JOIN to a RIGHT JOIN:

SELECT sales_rep_id,
CONCAT(name_first, SPACE(1), name_last) AS rep_name,
FORMAT(AVG(sale_amount), 2) AS avg_sales
FROM sales
RIGHT JOIN sales_reps USING(sales_rep_id)
GROUP BY sales_rep_id;

Sales representatives who made no sales will show up with NULL in the avg_sales column. This version of the statement also includes an enhancement: it rounds the results for avg_sales to two decimal places ...