Функции SUM() и AVG() в SQL
Функция SUM() используется для вычисления суммы чисел в столбце. Функция AVG() используется для вычисления среднего арифметического чисел в столбце.
Функция SUM() в SQL
Функция SUM() используется для вычисления суммы чисел в столбце. Например:
Здесь мы получаем сумму всех заказов из таблицы Orders.
Рассмотрим другой пример:
Здесь мы получаем общую сумму, которую должен заплатить клиент с идентификатором 4 за все свои заказы.
Функция AVG() в SQL
Функция AVG() используется для вычисления среднего арифметического чисел в столбце. Например:
SQL AVG
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:
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:
However, the AVG(DISTINCT) will process as follows:
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:
Let’s apply the DISTINCT operator to see if the result changes:
It changed because some employees have the same salary.
To round the result to 2 decimal places, you use the ROUND function as follows:
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:
The following statement returns the average salary of employees who hold the job id 6:
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.
We can use the inner join clause to join the employees table with the departments table to get the department name data:
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:
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:
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.
Как работает avg в sql
Analytic Functions for information on syntax, semantics, and restrictions
AVG returns average value of expr .
This function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.
Table 2-8 for more information on implicit conversion
If you specify DISTINCT , then you can specify only the query_partition_clause of the analytic_clause . The order_by_clause and windowing_clause are not allowed.
About SQL Expressions for information on valid forms of expr and Aggregate Functions
The following example calculates the average salary of all employees in the hr.employees table:
The following example calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee:
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.