MySQL AVG

 MySQL AVG

Summary: in this tutorial, you will learn how to use MySQL AVG() function to calculate the average value of a set of values.

Introduction to MySQL AVG() function

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

Here is the basic 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 DISTINCT the operator will return 2 i.e., (1 + 2 + 3) / 3.

MySQL AVG() function examples

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

A) 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;

B) 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.

C) Using MySQL AVG with DISTINCT option example

This query checks if there are any products that 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 DISTINCT operator.

D) MySQL AVG with GROUP BY clause example

The AVG() the 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;

E) 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;

F) 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.

G) 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 AVG function:

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.

H) 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;

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.

In this tutorial, you have learned some useful techniques to calculate the average value of a set of values by using theAVG() function.