Как найти среднее значение в таблице sql

Summary: this tutorial, we will show you how to use SQL AVG function to get the average value of a set.

Introduction to SQL AVG function

The SQL AVG function is an aggregate function that calculates the average value of a set. The following illustrates the syntax of the SQL AVG function:

AVG([ALL|DISTINCT] expression)
Code language: SQL (Structured Query Language) (sql)

If we use the ALL keyword, the AVG function takes all values in the calculation. By default, the AVG function uses ALL whether we specify it or not.

If we specify the DISTINCT keyword explicitly, the AVG function will take the unique values only in the calculation.

For example, we have a set of (1,2,3,3,4) and apply the AVG(ALL) to this set, the AVG function will perform the following calculation:

(1+2+3+3+4)/5 = 2.6
Code language: SQL (Structured Query Language) (sql)

However, the AVG(DISTINCT) will process as follows:

(1+2+3+4)/4 = 2.5
Code language: SQL (Structured Query Language) (sql)

SQL AVG function examples

We will use the employees table in the sample database to demonstrate how the SQL AVG function works. The following picture illustrates the structure of the employees table:

To calculate the average salary of all employees, you apply the AVG function to the salary column as follows:

SELECT 
    AVG(salary)
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

Try It

Let’s apply the DISTINCT operator to see if the result changes:

SELECT 
    AVG(DISTINCT salary)
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

Try It

It changed because some employees have the same salary.

To round the result to 2 decimal places, you use the ROUND function as follows:

SELECT 
    ROUND(AVG(DISTINCT salary), 2)
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

Try It

To calculate the average value of a subset of values, we add a WHERE clause to the SELECT statement. For instance, to calculate the average salary of employees in the department id 5, we use the following query:

SELECT
	AVG(DISTINCT salary)
FROM
	employees
WHERE
	department_id = 5;
Code language: SQL (Structured Query Language) (sql)

Try It

The following statement returns the average salary of employees who hold the job id 6:

SELECT 
    AVG(salary)
FROM
    employees
WHERE
    job_id = 6;
Code language: SQL (Structured Query Language) (sql)

Try It

SQL AVG with GROUP BY clause example

To calculate the average values of groups, we use the AVG function with the GROUP BY clause. For example, the following statement returns the departments and the average salary of employees of each department.

SELECT
	department_id,
	AVG(salary)
FROM
	employees
GROUP BY
	department_id;
Code language: SQL (Structured Query Language) (sql)

Try It

We can use the inner join clause to join the employees table with the departments table to get the department name data:

SELECT
	e.department_id,
	department_name,
	AVG(salary)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id;
Code language: SQL (Structured Query Language) (sql)

Try It

SQL AVG with ORDER BY clause example

To sort the result set that includes the AVG results, you use the AVG function in the ORDER BY clause as follows:

SELECT
	e.department_id,
	department_name,
	AVG(salary)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id
ORDER BY
	AVG(salary) DESC;

Try It

SQL AVG with HAVING clause example

To filter group, you use the AVG function in the HAVING clause. For example, the following statement gets the department that has the average salary less than 5000:

SQL AVG with a subquery

We can apply AVG function multiple times in a single SQL statement to calculate the average value of a set of average values.

For example, we can use the AVG function to calculate the average salary of employees in each department, and apply the AVG function one more time to calculate the average salary of departments.

The following query illustrates the idea:

SELECT
	AVG(employee_sal_avg)
FROM
	(
		SELECT
			AVG(salary) employee_sal_avg
		FROM
			employees
		GROUP BY
			department_id
	) t;
Code language: SQL (Structured Query Language) (sql)

Try It

How the query works.

• The subquery returns a set of the average salaries of employees for each department.
• The outer query returns the average salary of departments.

In this tutorial, you have learned how to use the SQL AVG function to calculate the average value of a set.

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

Introduction to MySQL AVG() function

The MySQL AVG() function is an aggregate function that allows you to calculate the average value of a set.

Here is the basic syntax of the AVG() function:

AVG(DISTINCT expression)Code language: SQL (Structured Query Language) (sql)

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 operator will return 2 i.e., (1 + 2 + 3) / 3.

We will use the products table in the sample database for the demonstration:

A) Using MySQL AVG() function to calculate an average of all values in a column example

This example uses the AVG() function to calculate the average buy price of all products from the products table:

SELECT 
    AVG(buyprice) 'Average Price'
FROM
    products;Code language: SQL (Structured Query Language) (sql)

Try It Out

B) Using MySQL AVG() function with a WHERE clause example

The following  example uses the AVG() function to calculate the average buy price of products in the product line Classic Cars:

SELECT 
    AVG(buyprice) 'Average Classic Cars Price'
FROM
    products
WHERE
    productline = 'Classic Cars';Code language: SQL (Structured Query Language) (sql)

Try It Out

In this example, the WHERE clause has a condition that includes only the Classic Cars product line. Therefore, the AVG() function calculates the average value for the buy prices of products in Classic Cars only.

C) Using MySQL AVG with DISTINCT option example

This query checks if there are any products which have the same prices:

SELECT 
    COUNT(buyprice) - COUNT(DISTINCT buyprice)
FROM
    products;Code language: SQL (Structured Query Language) (sql)

Try It Out

This query uses the AVG() function with the DISTINCT option to calculate the average of distinct buy prices:

SELECT 
    FORMAT(AVG(DISTINCT buyprice), 2)
FROM
    products;Code language: SQL (Structured Query Language) (sql)

Try It Out

Notice that the result is different from the average buy price without using the DISTINCT operator.

D) MySQL AVG with GROUP BY clause example

The AVG() function is often used 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, 
    AVG(buyprice) 'Average Price'
FROM
    products
GROUP BY productline;Code language: SQL (Structured Query Language) (sql)

Try It Out

E) Using MySQL AVG() function with a HAVING clause example

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

SELECT 
    productline, 
    AVG(buyprice) 'Average Price'
FROM
    products
GROUP BY productline
HAVING AVG(buyprice) > 50;Code language: SQL (Structured Query Language) (sql)

Try It Out

F) Using MySQL AVG() function with a subquery example

You can use the AVG() function in an SQL statement multiple times to calculate the average value of a set of average values.

This query uses the AVG() function to calculate the average buy price of the average buy prices of product lines:

SELECT 
    AVG(pl_avg) 'Average Product'
FROM
    (SELECT 
        AVG(buyprice) pl_avg
    FROM
        products
    GROUP BY productline) avgs;Code language: SQL (Structured Query Language) (sql)

Try It Out

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.

G) Using MySQL AVG() function with NULL example

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.

CREATE TABLE IF NOT EXISTS t (
    id INT AUTO_INCREMENT PRIMARY KEY,
    val INT
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the t table, including NULL value.

INSERT INTO t(val)
VALUES(1),(2),(nulL),(3);Code language: SQL (Structured Query Language) (sql)

Third, calculate the average value of the values in the val column by using the AVG function:

SELECT 
    AVG(val)
FROM
    t;Code language: SQL (Structured Query Language) (sql)

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

H) Using MySQL AVG() function with control flow functions

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

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 
    AVG(IF(productline = 'Classic Cars',
        buyprice,
        NULL)) / AVG(buyprice) 'Classic Cars/ Products'
FROM
    products;Code language: SQL (Structured Query Language) (sql)

Try It Out

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

Because the AVG() function ignores the NULL values in the calculation so the AVG(IF(productline='Classic Cars',buyprice,NULL)) expression returns the average buy price for only products whose product line is Classic Cars.

In this tutorial, you have learned some useful techniques to calculate the average value of a set of values by using theAVG() function.