Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 by adding the FORMAT() function.

If we only want the average sales for the current month, we could add a WHERE clause. However, that would negate the effect of the RIGHT JOIN: sales people without orders for the month wouldn’t appear in the list. To include them, first we need to run a subquery that extracts the sales data that meets the conditions of the WHERE clause, and then we need to join the subquery’s results to another subquery containing a tidy list of the names of sales reps:

SELECT sales_rep_id, rep_name, 
IFNULL(avg_sales, 'none') as avg_sales_month
FROM
  (SELECT sales_rep_id,
   FORMAT(AVG(sale_amount), 2) AS avg_sales
   FROM sales
   JOIN sales_reps USING(sales_rep_id)
   WHERE DATE_FORMAT(date_of_sale, '%Y%m') = 
         DATE_FORMAT(CURDATE(), '%Y%m')
   GROUP BY sales_rep_id) AS active_reps
RIGHT JOIN 
  (SELECT sales_rep_id,
   CONCAT(name_first, SPACE(1), name_last) AS rep_name
   FROM sales_reps) AS all_reps
USING(sales_rep_id)
GROUP BY sales_rep_id;

In the first subquery here, we are determining the average sales for each sales rep that had sales for the current month. In the second subquery, we’re putting together a list of names of all sales reps, regardless of sales. In the main query, using the sales_rep_id column as the joining point of the two results sets derived from the subqueries, we are creating a results set that will show the average sales for the month for each rep that had some sales, or (using IFNULL()) the word none for those who had none.