Sql как найти среднее значение средних значений

In this article, we are going to see how to find the average value in a column in SQL.  A column in the SQL table is the vertical catalog structure. In this article, we will be using the Microsoft SQL Server as our database.

For the purpose of example, we will be creating a sample table and performing the same operations on it.

Table Definition:

We have the following car table in our database :

CREATE TABLE car (
companyid integer ,
companyname varchar(20) ,
totalmodels integer )

The above query creates a car table for us.

Adding Data to Table:

To insert values into the table we need to use the insert statement. Use the below statement to add data to the car table:

INSERT INTO car values(1,'maruti  suzuki',10);
INSERT INTO car values(2,'tata',12);
INSERT INTO car values(3,'volkswagen',8);

The above query will add the data to the car table.

Note: We have to insert values according to the table created. For example, we created a car table with companyid as integer, companyname as varchar, and totalmodels as an integer. So, we need to insert an integer, a character, and an integer else we may get an error.

To verify the contents of the table use the below statement:

SELECT * FROM car;

This will show us our created table as shown below:

Average of all values in a column

For this, we need to use avg() function. We have to pass the column name as a parameter. The avg() function has the following syntax:

SELECT AVG( column_name ) FROM table_name;
  • The avg() function can be used with the SELECT query for retrieving data from a table.

The below query can be used to find the average of the totalmodels column of our cars table:

SELECT AVG(totalmodels) FROM car;

Output :

Let’s verify the output. The average of 10, 12, and 18 is 10.  Hence, we have successfully found out the average of all values n a column of a table.

Finding out the Average without using the AVG() function –

In this approach we will see how we can find out the average value of a column without using the AVG() function. For this purpose we will use two function SUM() and COUNT(). Firstly we will sum all of the values of a certain column and then divide it with the number of elements of that column.

Considering the table is already created and the values are being inserted.

Syntax of SUM()

SELECT SUM(Col_name) FROM Table_Name;

Syntax of COUNT() 

SELECT COUNT(Col_Name) FROM Table_Name;

SQL Code –

SELECT SUM(totalmodels) / COUNT (totalmodels) AS Average FROM car;

Output – 

Here in this code an alias “Average” has been used to make the output column name “Average”, if the user doesn’t want to use that then they might skip this.

Last Updated :
25 Mar, 2023

Like Article

Save Article

Источник

В этом учебном материале вы узнаете, как использовать SQL функцию AVG с синтаксисом и примерами.

Описание

SQL функция AVG используется для возврата среднего значения выражения в операторе SELECT.

Синтаксис

Синтаксис для функции AVG в SQL.

SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];

Или синтаксис для функции AVG при группировке результатов по одному или нескольким столбцам.

SELECT expression1, expression2, … expression_n,
AVG(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n;

Параметры или аргумент

expression1, expression2, … expression_n
Выражения, которые не инкапсулированы в функции AVG и должны быть включены в предложение GROUP BY в конце SQL запроса
aggregate_expression
Это столбец или выражение, которое будет усреднено
tables
Таблицы, из которых вы хотите получить записи. В предложении FROM должна быть указана хотя бы одна таблица
WHERE conditions
Необязательный. Это условия, которые должны быть выполнены для выбора записей

Пример — с одним выражением

Например, вы, возможно, захотите узнать, какова средняя стоимость всех товаров, входящих в категорию Clothing.

SELECT AVG(cost) AS «Average Cost»

  FROM products

WHERE category = ‘Clothing’;

В этом примере SQL функции AVG выражению AVG(cost) мы указали псевдоним «Average Cost». В результате «Average Cost» будет отображаться как имя поля при возврате набора результатов.

Пример — использование SQL DISTINCT

Вы можете использовать SQL DISTINCT в функции AVG. Например, приведенная ниже инструкция SELECT возвращает совокупную среднюю стоимость уникальных значений стоимости, где категория — Clothing.

SELECT AVG(DISTINCT cost) AS «Average Cost»

  FROM products

WHERE category = ‘Clothing’;

Если бы было два стоимостных значения в 25 $, то только одно из этих значений будет использовано при расчете функции AVG.

Пример — использование формул

expression, содержащееся в функции AVG, необязательно должно быть одним полем. Вы также можете использовать формулу. Например, вы можете получить среднюю прибыль за продукт. Средняя прибыль рассчитывается как sale_price за вычетом cost.

SELECT AVG(sale_price — cost) AS «Average Profit»

  FROM products;

Вы также можете выполнить математическую операцию в функции AVG. Например, вы можете определить среднюю комиссию как 10% от sale_price.

SELECT AVG(sale_price * 0.10) AS «Average Commission»

  FROM products;

Пример — использование SQL GROUP BY

В некоторых случаях вам потребуется использовать предложение SQL GROUP BY с функцией AVG.
Например, вы также можете использовать функцию AVG, чтобы вернуть название отдела и средние продажи (в связанном отделе).

SELECT department,

   AVG(sales) AS «Average Sales»

  FROM order_details

WHERE department > 10

GROUP BY department;

Поскольку в операторе SELECT вы указали один столбец, который не инкапсулирован в функции AVG, вы должны использовать предложение GROUP BY, поэтому поле department должно быть указано в разделе GROUP BY.

Источник

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:

products table

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

MySQL AVG function - average product price

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

MySQL AVG function - Average Classic Cars Price

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

MySQL AVG function - COUNT function

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

MySQL AVG function - average distinct product 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:

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

Try It Out

MySQL AVG function - average buy price by product line

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

MySQL AVG function - with GROUP BY clause

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

MySQL AVG function - with subquery example

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)

MySQL AVG function - NULL example

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

MySQL AVG function - with control flow function

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.

Was this tutorial helpful?

Источник

Группировка

Агрегатные функции

Последнее обновление: 19.07.2017

Агрегатные функции выполняют вычисления над значениями в наборе строк. В T-SQL имеются следующие агрегатные функции:

  • AVG: находит среднее значение

  • SUM: находит сумму значений

  • MIN: находит наименьшее значение

  • MAX: находит наибольшее значение

  • COUNT: находит количество строк в запросе

В качестве аргумента все агрегатные функции принимают выражение, которое представляет критерий дя определения значений. Зачастую, в качестве
выражения выступает название столбца, над значениями которого надо проводить вычисления.

Выражения в функциях AVG и SUM должно представлять числовое значение. Выражение в функциях
MIN, MAX и COUNT может представлять числовое или строковое значение или дату.

Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL.

Avg

Функция Avg возвращает среднее значение на диапазоне значений столбца таблицы.

Пусть в базе данных у нас есть таблица товаров Products, которая описывается следующими выражениями:

CREATE TABLE Products
(
    Id INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(30) NOT NULL,
    Manufacturer NVARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price MONEY NOT NULL
);
 
INSERT INTO Products 
VALUES
('iPhone 6', 'Apple', 3, 36000),
('iPhone 6S', 'Apple', 2, 41000),
('iPhone 7', 'Apple', 5, 52000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000),
('Mi6', 'Xiaomi', 5, 28000),
('OnePlus 5', 'OnePlus', 6, 38000)

Найдем среднюю цену товаров из базы данных:

SELECT AVG(Price) AS Average_Price FROM Products

Для поиска среднего значения в качестве выражения в функцию передается столбец Price. Для получаемого значения устанавливается псевдоним Average_Price, хотя можно его и не
устанавливать.

Функция avg в T-SQL

Также мы можем применить фильтрацию. Например, найти среднюю цену для товаров какого-то определенного производителя:

SELECT AVG(Price) FROM Products
WHERE Manufacturer='Apple'

И, кроме того, мы можем находить среднее значение для более сложных выражений.
Например, найдем среднюю сумму всех товаров, учитывая их количество:

SELECT AVG(Price * ProductCount) FROM Products

Count

Функция Count вычисляет количество строк в выборке. Есть две формы этой функции.
Первая форма COUNT(*) подсчитывает число строк в выборке:

SELECT COUNT(*) FROM Products

Функция count в T-SQL в MS SQL Server

Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:

SELECT COUNT(Manufacturer) FROM Products

Min и Max

Функции Min и Max возвращают соответственно минимальное и максимальное значение по столбцу.
Например, найдем минимальную цену среди товаров:

SELECT MIN(Price) FROM Products

Поиск максимальной цены:

SELECT MAX(Price) FROM Products

Данные функции также игнорируют значения NULL и не учитывают их при подсчете.

Sum

Функция Sum вычисляет сумму значений столбца. Например, подсчитаем общее количество товаров:

SELECT SUM(ProductCount) FROM Products

Функция SUM в T-SQL

Также вместо имени столбца может передаваться вычисляемое выражение. Например, найдем общую стоимость всех имеющихся товаров:

SELECT SUM(ProductCount * Price) FROM Products

All и Distinct

По умолчанию все вышеперечисленных пять функций учитывают все строки выборки для вычисления результата. Но выборка может содержать повторяющие значения.
Если необходимо выполнить вычисления только над уникальными значениями, исключив из набора значений повторяющиеся данные, то для
этого применяется оператор DISTINCT.

SELECT AVG(DISTINCT ProductCount) AS Average_Price FROM Products

По умолчанию вместо DISTINCT применяется оператор ALL, который выбирает все строки:

SELECT AVG(ALL ProductCount) AS Average_Price FROM Products

Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.

Комбинирование функций

Объединим применение нескольких функций:

SELECT COUNT(*) AS ProdCount,
	   SUM(ProductCount) AS TotalCount,
       MIN(Price) AS MinPrice,
       MAX(Price) AS MaxPrice,
       AVG(Price) AS AvgPrice
FROM Products

Агрегатные функции в T-SQL и MS SQL Server

Источник

Функция SQL AVG

Функция SQL AVG необходимо в случае, если требуется вычислить среднее значение числового столбца в таблице. Среднее значение функция AVG() в SQL вычисляет среднее значение столбца путем суммирования всех значений записей столбца и деления на количество записей.

AVG SQL Примеры

Рассмотрим пример. Допустим в таблице Price есть столбец Price_unit. В этой таблице содержатся 5 записей. Значения полей столбца Price_unit 3, 5, 14, 38 и 83.

Выполним запрос, возвращающий среднее значение столбца Price_unit

SELECT AVG(Price_unit) AS PriceAvg FROM Price;

Результатом выполнения данного запроса будет

PriceAvg = 28,6

Источник

Понравилась статья? Поделить с друзьями:
  • Как найти повторяющиеся фотографии на компьютере
  • Как найти выставочный зал
  • Как найти документ word по словам
  • Как найти причастие в тексте онлайн
  • Как исправить дефекты посадки плечевых изделий