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

## 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 or an expression.

The syntax of the `AVG`

function is as follows:

1 | 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`

operation will return two i.e., ` (1 + 2 + 3) / 2`

.

## MySQL AVG examples

We are going to use the `products`

table in the sample database for the demonstration. The following diagram illustrates the `products`

table.

To calculate average buy price of all products in the `products`

table, you use the `AVG`

function as the following query:

1 2 | SELECT AVG(buyprice)'Avarage Price' FROM products; |

Notice that the `FORMAT`

function is used to format the average value returned by the `AVG`

function.

You can add a WHERE clause to the SELECT statement to calculate the average value of a subset of values. For example, to calculate the average buy price of products whose product line is `Classic Cars`

, you use the following query:

1 2 3 | SELECT AVG(buyprice) 'Avarage Classic Cars Price' FROM products WHERE productline = 'Classic Cars'; |

### MySQL AVG with DISTINCT

Some products have the same price. You can check it by using the following query:

1 2 | SELECT COUNT(buyprice) - COUNT(DISTINCT buyprice) FROM products; |

You can use `AVG`

function to calculate the average of distinct buy prices by adding the `DISTINCT`

operator as follows:

1 2 | SELECT AVG(DISTINCT buyprice) FROM products; |

The result is slightly different from the average buy price without using the `DISTINCT`

operator.

### MySQL AVG with GROUP BY clause

We often use the `AVG`

function 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:

1 2 3 4 | SELECT productline, AVG(buyprice) 'Avarage Price' FROM products GROUP BY productline; |

### MySQL AVG with HAVING clause

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 product’s average buy prices are greater than 50, you can use the following query:

1 2 3 4 5 | SELECT productline, AVG(buyprice) 'Avarage Price' FROM products GROUP BY productline HAVING AVG(buyprice) > 50; |

### MySQL AVG function with subquery

You can use the `AVG`

function in SQL statement multiple times to calculate the average value of a set of average values. For example, you can calculate the average buy price of the average buy prices of product lines as the following query:

1 2 3 4 5 6 | 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.

### MySQL AVG function with NULL values

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.

1 2 3 4 | 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.

1 2 | 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:

1 | 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.

### MySQL AVG with control flow function

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, NULLIF, etc.

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:

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

The `IF(productline='Classic Cars',buyprice,NULL)`

expression returns buy price if the product line is `Classic Cars`

, otherwise it returns `NULL`

.

Because the `AVG`

function ignores the `NULL`

values in the calculation so the `AVG(IF(productline='Classic Cars',buyprice,NULL))`

expression calculates the average buy price for only products whose product line is `Classic Cars`

.

In this tutorial, we have shown you some useful techniques to calculate the average value of a set of values by using MySQL AVG function.