Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to find the average in mysql". The content is simple and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn this article "how to find the average in mysql".

In mysql, you can use the AVG () function to average, which can average the data of a specified column by calculating the number of rows returned and the sum of each row of data; 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.

The MySQL AVG () function is an aggregate function that calculates the average of a set of values or expressions.

The AVG () function can calculate the average of the specified column data by calculating the number of rows returned 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 a DISTINCT operation returns the sum of the different values, that is, (1 + 2 + 3) / 3 = 2.00.

MySQL AVG example

We will demonstrate using the products table in the sample database. The following figure shows 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 (10L2) | NO | | NULL | | MSRP | decimal (10L2) | 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 and get the following results-

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

Note that the FORMAT function is used to format the average returned by the AVG function.

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

SELECT AVG (buyprice) 'Avarage Classic Cars Price'FROM productsWHERE productline =' Classic Cars'

Execute the above query and get the following results-

Mysql > SELECT AVG (buyprice) 'Avarage Classic Cars Price'FROM productsWHERE productline =' Classic Cars' +-- + | Avarage Classic Cars Price | +-- + | 64.446316 | +-+ 1 row in set has the MySQL AVG () function of DISTINCT

Some products have the same price, which can be checked using the following query:

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

Execute the above query 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 average different prices by adding the DISTINCT operator, as shown below:

SELECT AVG (DISTINCT buyprice) FROM products

Execute the above query 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 from 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 average of each set of rows in the table.

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

SELECT productline, AVG (buyprice) 'Avarage Price'FROM productsGROUP BY productline

Execute the above query and get the following results-

Mysql > SELECT productline, AVG (buyprice) 'Avarage Price'FROM productsGROUP 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 clause in the AVG function to set the condition for the average value of the group. For example, if you want to select only product lines where the 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 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 setMySQL 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 a 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 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 does it work-

The subquery calculates the average purchase price according to the product line.

The external query calculates the average purchase price of the product line returned from the subquery.

MySQL AVG function with null value

The AVG () function ignores null values in the calculation, see the following example:

First, create a new table called t with two columns, id and val,val, that 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 and get the following results-

Mysql > SELECT AVG (val) FROM tbot row in set + | AVG (val) | +-+ | 2.0000 | +-+ 1 row in set

This statement returns 2 as expected because null values are not included in the calculation of the AVG function.

MySQL AVG with control stream function

To average a column and conditionally average the same column in a single statement, you can use the AVG function with control flow functions such as IF,CASE,IFNULL,NULLIF, and so on.

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

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

Execute the above query 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, 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 average price of products whose product line is Classic Cars.

The above is all the contents of the article "how to find the average in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.