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

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

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

Агрегатные функции вычисляют некоторые скалярные значения в наборе строк. В MySQL есть следующие агрегатные функции:

  • AVG: вычисляет среднее значение

  • SUM: вычисляет сумму значений

  • MIN: вычисляет наименьшее значение

  • MAX: вычисляет наибольшее значение

  • COUNT: вычисляет количество строк в запросе

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

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

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

Avg

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

Например, пусть есть следующая таблица товаров Products:

CREATE TABLE Products
(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    ProductName VARCHAR(30) NOT NULL,
    Manufacturer VARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price DECIMAL NOT NULL
);
  
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price) 
VALUES
('iPhone X', 'Apple', 3, 76000),
('iPhone 8', 'Apple', 2, 51000),
('iPhone 7', 'Apple', 5, 32000),
('Galaxy S9', 'Samsung', 2, 56000),
('Galaxy S8', 'Samsung', 1, 46000),
('Honor 10', 'Huawei', 5, 28000),
('Nokia 8', 'HMD Global', 6, 38000)

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

SELECT AVG(Price) AS Average_Price FROM Products

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

Функция avg и поиск среднего значения в MySQL

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

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

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

SELECT AVG(Price * ProductCount) FROM Products

Count

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

SELECT COUNT(*) FROM Products

Функция count в MySQL и вычисление количества строк

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

SELECT COUNT(Manufacturer) FROM Products

Min и Max

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

SELECT MIN(Price), MAX(Price) FROM Products

MIN и MAX в MySQL

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

Sum

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

SELECT SUM(ProductCount) FROM Products

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

SELECT SUM(ProductCount * Price) FROM Products

All и Distinct

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

SELECT COUNT(DISTINCT Manufacturer) FROM Products

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

SELECT COUNT(ALL Manufacturer) FROM Products

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

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

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

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

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

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?

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

Описание

MySQL функция AVG возвращает среднее значение выражения.

Синтаксис

Синтаксис MySQL функции AVG:

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

ИЛИ Синтаксис MySQL функции 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 — необязательный. Это условия, которые должны быть выполнены для выбранных записей.

Применение

Функция AVG может использоваться в следующих версиях MySQL:

  • MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

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

Например, вы можете узнать, какая средняя зарплата всех сотрудников, чья зарплата превышает 12 000 долларов в год.

Например:

SELECT AVG(salary) AS «Средняя зарплата»

  FROM employees

WHERE salary > 12000;

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

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

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

SELECT AVG(DISTINCT salary) AS «Средняя зарплата»

  FROM employees

WHERE salary > 12000;

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

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

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

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

  FROM orders;

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

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

SELECT department, AVG(sales) AS «Avg sales»

  FROM order_details

GROUP BY department;

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

In this tutorial, we will learn about the following MySQL aggregate functionsCOUNT(), SUM() and AVG(). Suppose we want to count the number of employees from a table who are in the Marketing department. Or what if we wanted to find the average salary of employees in a company?

How about situations in which we would like to calculate the total amount spent by a company paying its employees? MySQL provides us with aggregate functions like COUNT(), SUM() and AVG() for these purposes.

  • COUNT() is used to count the number of rows for a given condition. COUNT() works on numeric as well as non-numeric values.
  • SUM() is used to calculate the total sum of all values in the specified numeric column.
  • AVG() is used to calculate the average value of the specified numeric column.

Note that, all three functions ignore NULL values.


Syntax of MySQL Aggregate Functions

Let’s go over the syntax of all the three functions here.

1. Syntax for COUNT()

SELECT COUNT(expression) FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)

2. Syntax for SUM()

SELECT SUM(column_name) FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)

3. Syntax for AVG()

SELECT AVG(column_name) FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)

You will notice that only the COUNT() function has expression in its argument as opposed to column_name in SUM() and AVG(). This is because we have a special implementation of COUNT() which is COUNT(*). COUNT (*) returns the count of all the rows, including the ones with NULL values, in a specified table.


Examples of MySQL COUNT()

Consider the below ConferenceGuests table.

Count Conference Table
Conference Table

1. Simple example using COUNT()

Let us begin with a simple example demonstrating the COUNT() function. Let us count the number of records in the above table. We do so using the SELECT query as shown below:

SELECT COUNT(*) FROM ConferenceGuests;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Count Simple Example

If we look closely at the ConferenceGuests table, we see that the record for guest with ID – 9 is missing. Hence, there are 15 records.

2. Counting Unique Values In A Column

Let us count the unique values in the Country column. For this, we will use the DISTINCT Keyword. We use the following query,

SELECT COUNT(DISTINCT Country) FROM ConferenceGuests;

The COUNT() function only counts the unique values in the Country column and returns the output as follows,

Count Distinct Example

3. Using COUNT() With the WHERE Clause

Let us now use COUNT() and specify a condition using the WHERE clause. How many unique states from India are represented in the conference?

SELECT COUNT(DISTINCT State) FROM ConferenceGuests WHERE Country='India';Code language: SQL (Structured Query Language) (sql)

The output is as follows,

 MySQL Aggregate functions  Count Where Clause

4. Using COUNT() With the LIKE Clause

Let us take a more complex example. Let us use the LIKE clause with the COUNT() function. How about finding the number of guests whose name begins with an ‘A’. The query for that is:

SELECT COUNT(Name) FROM ConferenceGuests WHERE Name LIKE 'A%';Code language: SQL (Structured Query Language) (sql)

The output is as follows,

Count Like Clause

Examples of MySQL SUM()

Consider the below Employee table.

 MySQL Aggregate functions  Sum Employee Table
Employee Table

1. Simple Example of SUM()

Let us calculate the sum of the Salary column. We do so using the query,

SELECT SUM(Salary) FROM Employee;Code language: SQF (sqf)

The sum of the Salary column is returned in the output below,

Sum Simple Example

2. Using SUM() With the WHERE Clause

How about finding the sum of salaries of all employees in the Operations department?

SELECT SUM(Salary) FROM Employee WHERE Department='Operations';Code language: SQL (Structured Query Language) (sql)

We get the following output,

Sum Where Clause

3. Using SUM() with the IN Operator

Let us take a little complex example now. How about finding the salaries of employees who have ‘MUM’ and ‘PUN’ as their Office_Code values? We will take the help of the IN Operator and the query is as follows:

SELECT SUM(Salary) FROM Employee WHERE Office_Code IN ('MUM', 'PUN');Code language: SQL (Structured Query Language) (sql)

We get the following output,

Sum In Operator

Examples of MySQL AVG()

Let us continue with the Employee table.

1. Simple Example of AVG()

Let us find the average salary of all employees in the company. We use the below query:

SELECT AVG(Salary) FROM Employee;Code language: SQL (Structured Query Language) (sql)

And we get the output as:

Avg Simple Example MySQL Aggregate functions

As you can see, we get the average value of the Salary column.

2. Using AVG() with the WHERE Clause

Just like COUNT() and SUM(), AVG() can be used with different clauses. Here, we will demonstrate how we use it with the WHERE clause. How about finding out the average salary in the Executive department?

SELECT AVG(Salary) FROM Employee WHERE Department='Executive';Code language: SQL (Structured Query Language) (sql)

And we get the output as follows,

MySQL Aggregate functions Avg Where Clause

Conclusion

MySQL aggregate functions like COUNT(), SUM() and AVG() prove essential in data analysis in large tables and databases. I would highly recommend you to check out the below reference link.


References

  • MySQL official documentation on aggregate functions.

Not pretty, but it works.

Not specifically MySql, but the idea should be easy enough to translate over.

CREATE TABLE A (id int identity(1,1), C1 int, C2 int, C3 int)
GO

INSERT INTO A VALUES (1,1,1)
INSERT INTO A VALUES (2,2,2)
INSERT INTO A VALUES (3,3,3)
INSERT INTO A VALUES (1,2,3)
INSERT INTO A VALUES (4,5,6)
GO

CREATE VIEW A_Values
AS
SELECT ID, AVG(Val) AS Average 
FROM
(
    SELECT ID, C1 AS Val FROM A
    UNION ALL
    SELECT ID, C2 AS Val FROM A
    UNION ALL
    SELECT ID, C3 AS Val FROM A
) Q
GROUP BY ID
GO


SELECT * FROM A_Values
GO

Понравилась статья? Поделить с друзьями:
  • Как найти ctg острого угла
  • Как найти бойца свободы
  • Vmware cannot connect to the virtual machine как исправить
  • Как можно найти иностранного друга
  • Как быстрее найти клад