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 values1,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 theproductstable in the sample database for demonstration. The following figure is the structure of theproductstable-

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 theproductstable, 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 theFORMATfunction is used to format the average value returned by theAVGfunction.

You can add aWHEREclause to aSELECTstatement to calculate the average of a subset of values. For example, to calculate the average price of products with the product lineClassic 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 theDISTINCToperator, 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 theDISTINCToperator.

MySQL AVG with GROUP BY clause

We often use the AVG function with theGROUP BYclause 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 theGROUP BYclause, 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 theHAVINGsub-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 than50, 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 ignoresNULLvalues in calculations, see the following example:

First, create a new table namedtwith two columnsidandval,valcolumns can containNULLvalues.

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

Second, insert some rows in thettable, includingNULLvalues.

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

Third, use theAVG()function to calculate the average of the values in thevalcolumn:

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 returns2as expected because theNULLvalue 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 asIF,CASE,IFNULL,NULLIF, etc.).

For example, to calculate the ratio of the average price of theClassic Carsproduct 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 isClassic Cars, then theIF(productline='Classic Cars',buyprice,NULL)expression returns the price, otherwise it returnsNULL.

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

[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!