How to find the average value in mysql

In mysql, you can use the AVG() function to find the average. This function can find the average of the specified column data by calculating the number of returned rows and the sum of each row of data; the syntax "SELECT AVG (column_name) FROM table_name".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, you can use the AVG() function to find the average.

MySQL AVG() function is an aggregate function that is used to calculate the average of a set of values ​​or expressions.

The AVG() function can find the average of the specified column data by calculating the number of returned rows and the sum of each row of data.

The syntax of the AVG() function is as follows:

SELECT AVG(column_name) FROM table_name;

The DISTINCT operator can also be added to the AVG() function to calculate the average of different values. For example, if you have a set of values ​​1, 1, 2, 3, the AVG() function with the DISTINCT operation will return different The sum of the values ​​is: (1 2 3)/3 = 2.00.

MySQL AVG Example

We will use the products table in the sample database for demonstration. The following figure is the structure of the products table-

mysql> desc products;
+--------------------+---------------+------+-----+---------+------------------+
| Field              | Type          | Null | Key | Default | Extra            |
+--------------------+---------------+------+-----+---------+------------------+
| productCode        | varchar(15)   | NO   | PRI |         |                  |
| productName        | varchar(70)   | NO   | MUL | NULL    |                  |
| productLine        | varchar(50)   | NO   | MUL | NULL    |                  |
| productScale       | varchar(10)   | NO   |     | NULL    |                  |
| productVendor      | varchar(50)   | NO   |     | NULL    |                  |
| productDescription | text          | NO   |     | NULL    |                  |
| quantityInStock    | smallint(6)   | NO   |     | NULL    |                  |
| buyPrice           | decimal(10,2) | NO   |     | NULL    |                  |
| MSRP               | decimal(10,2) | NO   |     | NULL    |                  |
| stockValue         | double        | YES  |     | NULL    | STORED GENERATED |
+--------------------+---------------+------+-----+---------+------------------+
10 rows in set

To calculate the average price of all products in the products table, you can use the AVG function, as shown in the following query:

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

Execute the above query statement and get the following results-

mysql> SELECT AVG(buyprice) 'Avarage Price' FROM products;
+---------------+
| Avarage Price |
+---------------+
| 54.395182     |
+---------------+
1 row in set

Please note that the FORMAT function is used to format the average value returned by the AVG function.

You can add a WHERE clause to a SELECT statement to calculate the average of a subset of values. For example, to calculate the average price of products with the product line Classic Cars, you can use the following query:

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

Execute the above query statement and get the following results-

mysql> SELECT AVG(buyprice) 'Avarage Classic Cars Price'
FROM products
WHERE productline = 'Classic Cars';
+----------------------------+
| Avarage Classic Cars Price |
+----------------------------+
| 64.446316                  |
+----------------------------+
1 row in set

MySQL AVG() function with DISTINCT

Some products have the same price, you can use the following query to check it:

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

Execute the above query statement and get the following results-

mysql> SELECT COUNT(buyprice) - COUNT(DISTINCT buyprice) FROM products;
+--------------------------------------------+
| COUNT(buyprice) - COUNT(DISTINCT buyprice) |
+--------------------------------------------+
|                                          2 |
+--------------------------------------------+
1 row in set

You can use the AVG() function to calculate the average of different prices by adding the DISTINCT operator, as shown below:

SELECT AVG(DISTINCT buyprice) FROM products;

Execute the above query statement and get the following results -

mysql> SELECT AVG(DISTINCT buyprice) FROM products;
+------------------------+
| AVG(DISTINCT buyprice) |
+------------------------+
| 54.372870              |
+------------------------+
1 row in set

The result is slightly different than the average price using the DISTINCT operator.

MySQL AVG with GROUP BY clause

We often use the AVG function with the GROUP BY clause to calculate the value of each group of rows in the table average value.

For example, to calculate the average price of products for each product line, you would use the AVG function with the GROUP BY clause, as in the following query:

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

Execute the above query statement and get the following results-

mysql> SELECT productline,
       AVG(buyprice) 'Avarage Price'
FROM products
GROUP BY productline;
+------------------+---------------+
| productline      | Avarage Price |
+------------------+---------------+
| Classic Cars     | 64.446316     |
| Motorcycles      | 50.685385     |
| Planes           | 49.629167     |
| Ships            | 47.007778     |
| Trains           | 43.923333     |
| Trucks and Buses | 56.329091     |
| Vintage Cars     | 46.066250     |
+------------------+---------------+
7 rows in set

MySQL AVG with HAVING clause

You can use the HAVING sub-child in the AVG function Sentence sets conditions for group averages. For example, if you want to select only product lines whose average product price is greater than 50, you can use the following query:

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

Execute the above query statement and get the following results-

mysql> SELECT productline, AVG(buyprice) 'Avarage Price' FROM products GROUP BY productline HAVING AVG(buyprice) > 50;
+------------------+---------------+
| productline      | Avarage Price |
+------------------+---------------+
| Classic Cars     | 64.446316     |
| Motorcycles      | 50.685385     |
| Trucks and Buses | 56.329091     |
+------------------+---------------+
3 rows in set

MySQL AVG() function and subquery

You can use the AVG() function multiple times in a SQL statement to calculate the average of a set of averages. For example, the average purchase price of the product line can be calculated as follows:

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

Execute the above query statement and get the following results-

mysql> SELECT AVG(pl_avg) 'Average Product'
FROM (
    SELECT AVG(buyprice) pl_avg
    FROM products
    GROUP BY productline
) avgs;
+-----------------+
| Average Product |
+-----------------+
| 51.1553314286   |
+-----------------+
1 row in set

How to run-

• SubqueryCalculate the average purchase price based on product line.
• The outer query calculates the average purchase price of the product line returned from the subquery.

MySQL AVG function with NULL values ​​

The AVG() function ignores NULL values ​​in calculations, see the following example:

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

CREATE TABLE IF NOT EXISTS t(
    id  int auto_increment primary key,
    val int
);

Second , insert some rows in the t table, including NULL values.

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

Third , use the AVG() function to calculate the average of the values ​​in the val column:

SELECT AVG(val) FROM t;

Execute The above query statement yields the following result -

mysql> SELECT AVG(val) FROM t;
+----------+
| AVG(val) |
+----------+
| 2.0000   |
+----------+
1 row in set

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

MySQL AVG with control flow functions

To calculate the average of a column and conditionally calculate the average of the same column in a single statement, you can use AVG functions that control flow functions (such as IF, CASE, IFNULL, NULLIF, etc.).

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

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

Execute the above query statement and get The following results-

mysql> SELECT AVG(IF(productline='Classic Cars',buyprice,NULL)) / AVG(buyprice) 'Classic Cars/ Products'
FROM products;
+------------------------+
| Classic Cars/ Products |
+------------------------+
| 1.1847798580           |
+------------------------+
1 row in set

If the product line is Classic Cars, then the IF(productline='Classic Cars',buyprice,NULL) expression returns the price, otherwise it returns NULL.

Because the AVG function ignores the NULL value in the calculation, the AVG(IF(productline ='Classic Cars', buyprice, NULL)) expression only calculates the product line is The average price of Classic Cars products.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to find the average value in mysql. For more information, please follow other related articles on the PHP Chinese website!