SQL функция AVG
В этом учебном материале вы узнаете, как использовать SQL функцию AVG с синтаксисом и примерами.
Описание
SQL функция AVG используется для возврата среднего значения выражения в операторе SELECT.
Синтаксис
Синтаксис для функции AVG в SQL.
Или синтаксис для функции AVG при группировке результатов по одному или нескольким столбцам.
Параметры или аргумент
Пример — с одним выражением
Например, вы, возможно, захотите узнать, какова средняя стоимость всех товаров, входящих в категорию Clothing.
В этом примере SQL функции AVG выражению AVG(cost) мы указали псевдоним «Average Cost». В результате «Average Cost» будет отображаться как имя поля при возврате набора результатов.
Пример — использование SQL DISTINCT
Вы можете использовать SQL DISTINCT в функции AVG. Например, приведенная ниже инструкция SELECT возвращает совокупную среднюю стоимость уникальных значений стоимости, где категория — Clothing.
AVG SQL
Функция SQL AVG необходимо в случае, если требуется вычислить среднее значение числового столбца в таблице. Среднее значение функция AVG() в SQL вычисляет среднее значение столбца путем суммирования всех значений записей столбца и деления на количество записей.
AVG SQL Примеры
Рассмотрим пример. Допустим в таблице Price есть столбец Price_unit. В этой таблице содержатся 5 записей. Значения полей столбца Price_unit 3, 5, 14, 38 и 83.
Выполним запрос, возвращающий среднее значение столбца Price_unit
Группировка
Агрегатные функции выполняют вычисления над значениями в наборе строк. В T-SQL имеются следующие агрегатные функции:
AVG : находит среднее значение
SUM : находит сумму значений
MIN : находит наименьшее значение
MAX : находит наибольшее значение
COUNT : находит количество строк в запросе
В качестве аргумента все агрегатные функции принимают выражение, которое представляет критерий дя определения значений. Зачастую, в качестве выражения выступает название столбца, над значениями которого надо проводить вычисления.
Выражения в функциях AVG и SUM должно представлять числовое значение. Выражение в функциях MIN , MAX и COUNT может представлять числовое или строковое значение или дату.
Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL.
Функция Avg возвращает среднее значение на диапазоне значений столбца таблицы.
Пусть в базе данных у нас есть таблица товаров Products, которая описывается следующими выражениями:
Найдем среднюю цену товаров из базы данных:
Для поиска среднего значения в качестве выражения в функцию передается столбец Price. Для получаемого значения устанавливается псевдоним Average_Price, хотя можно его и не устанавливать.
Также мы можем применить фильтрацию. Например, найти среднюю цену для товаров какого-то определенного производителя:
И, кроме того, мы можем находить среднее значение для более сложных выражений. Например, найдем среднюю сумму всех товаров, учитывая их количество:
Count
Функция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:
Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:
Min и Max
Функции Min и Max возвращают соответственно минимальное и максимальное значение по столбцу. Например, найдем минимальную цену среди товаров:
Поиск максимальной цены:
Данные функции также игнорируют значения NULL и не учитывают их при подсчете.
Функция Sum вычисляет сумму значений столбца. Например, подсчитаем общее количество товаров:
Также вместо имени столбца может передаваться вычисляемое выражение. Например, найдем общую стоимость всех имеющихся товаров:
All и Distinct
По умолчанию все вышеперечисленных пять функций учитывают все строки выборки для вычисления результата. Но выборка может содержать повторяющие значения. Если необходимо выполнить вычисления только над уникальными значениями, исключив из набора значений повторяющиеся данные, то для этого применяется оператор DISTINCT .
По умолчанию вместо DISTINCT применяется оператор ALL , который выбирает все строки:
Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.
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.
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:
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 .
MySQL AVG() function examples
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:
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 :
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:
This query uses the AVG() function with the DISTINCT option to calculate the average of distinct buy prices:
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:
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:
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:
- 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.
Second, insert some rows into the t table, including NULL value.
Third, calculate the average value of the values in the val column by using the AVG function:
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:
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 the AVG() function.