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

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:

SELECT FORMAT(AVG(buyprice),2) '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:

SELECT FORMAT(AVG(buyprice),2) '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:

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:

SELECT FORMAT(AVG(DISTINCT buyprice),2) 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:

SELECT productline, FORMAT(AVG(buyprice),2) '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:

SELECT productline, FORMAT(AVG(buyprice),2) '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:

SELECT FORMAT(AVG(pl_avg),2) '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 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.

MySQL AVG with control flow function

To calculate 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:

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

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

Because the AVG function ignores the NULL values in 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.

原文链接：http://outofmemory.cn/mysql/function/mysql-avg