This MySQL function returns the average or mean of a set of numbers given as the argument.
Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the 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.
The above 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, we need to run a subquery that extracts the sales data that meets the conditions of the WHERE clause first, then 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 has some sales, or (using IFNULL()) the word none for those who had none.