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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.