MySQL Function: AVG()

Calculates the average of non-NULL values

Introduction to MySQL AVG() function

The MySQL AVG() function is an aggregate function that allows you to calculate the average value of a set of values.

Here’s the syntax of the AVG() function:

AVG(DISTINCT expression)

You use the DISTINCT operator in the AVG function to calculate the average value of the distinct values.

For example, if you have a set of values 1,1,2,3, the AVG function with DISTINCToperator will return 2 i.e., (1 + 2 + 3) / 3.

MySQL AVG() function examples

We will use the products table in the sample database for the demonstration:

1) Using MySQL AVG() function to calculate an average of all values in a column example

This example uses the AVG() function to calculate the average buy price of all products from the products table:

SELECT
    AVG(buyprice) 'Average Price'
FROM
    products;

2) Using MySQL AVG() function with a WHERE clause example

The following  example uses the AVG() function to calculate the average buy price of products in the product line Classic Cars:

SELECT
    AVG(buyprice) 'Average Classic Cars Price'
FROM
    products
WHERE
    productline = 'Classic Cars';

In this example, the WHERE clause has a condition that includes only the Classic Cars product line. Therefore, the AVG() function calculates the average value for the buy prices of products in Classic Cars only.

3) Using MySQL AVG with DISTINCT option example

This query checks if there are any products which have the same prices:

SELECT
    COUNT(buyprice) - COUNT(DISTINCT buyprice)
FROM
    products;

This query uses the AVG() function with the DISTINCT option to calculate the average of distinct buy prices:

SELECT
    FORMAT(AVG(DISTINCT buyprice), 2)
FROM
    products;

Notice that the result is different from the average buy price without using the DISTINCToperator.

4) MySQL AVG with GROUP BY clause example

The AVG() function is often used in conjunction with the GROUP BY clause to calculate the average value for each group of rows in a table. For example, to calculate the average buy price of products for each product line, you use the AVG() function with the GROUP BY clause as the following query:

SELECT
    productline,
    AVG(buyprice) 'Average Price'
FROM
    products
GROUP BY productline;

5) Using MySQL AVG() function with a HAVING clause example

You can use the AVG() function in the HAVING clause to set conditions for the average values of groups.

For example, if you want to select only product lines that have the product’s average buy prices greater than 50, you can use the following query:

SELECT
    productline,
    AVG(buyprice) 'Average Price'
FROM
    products
GROUP BY productline
HAVING AVG(buyprice) > 50;

6) Using MySQL AVG() function with a subquery example

You can use the AVG() function in an SQL statement multiple times to calculate the average value of a set of average values. This query uses the AVG() function to calculate the average buy price of the average buy prices of product lines:

SELECT
    AVG(pl_avg) 'Average Product'
FROM
    (SELECT
        AVG(buyprice) pl_avg
    FROM
        products
    GROUP BY productline) avgs;

How it works.

• The subquery calculates the average buy price by product lines.
• The outer query calculates the average buy price of the average buy prices of product lines returned from the subquery.

7) Using MySQL AVG() function with NULL example

The AVG() function ignores NULL values in the calculation. See the following example:

First, create a new table named t with two columns id and val. The val column can contain NULL values.

CREATE TABLE IF NOT EXISTS t (
    id INT AUTO_INCREMENT PRIMARY KEY,
    val INT
);

Second, insert some rows into the t table, including NULL value.

INSERT INTO t(val)
VALUES(1),(2),(nulL),(3);

Third, calculate the average value of the values in the val column by using the AVGfunction:

SELECT
    AVG(val)
FROM
    t;

The statement returns 2 as expected because the NULL value is not included in the calculation of the AVG function.

8) Using MySQL AVG() function with control flow functions

To calculate the average value of a column and calculate the average value of the same column conditionally in a single statement, you use AVG() function with control flow functions e.g., IF, CASE, IFNULL, and NULLIF.

For example, to calculate the ratio of the average buy price of Classic Cars product line to average buy price of all products, you use the following statement:

SELECT
    AVG(IF(productline = 'Classic Cars', buyprice, NULL)) / AVG(buyprice) 'Classic Cars/ Products'
FROM
    products;

Try It Out

The IF(productline='Classic Cars',buyprice,NULL) expression returns the buy price if the product line is Classic Cars, otherwise NULL.

Because the AVG() function ignores the NULL values in the calculation so the AVG(IF(productline='Classic Cars',buyprice,NULL)) expression returns the average buy price for only products whose product line is Classic Cars.

Summary

• Use the AVG() function to calculate the average value of a set of values.
• Use the AVG() function with the GROUP BY clause to calculate the average value for each group.