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

SQL Функции COUNT(), AVG() и SUM()

SQL COUNT(), AVG() и SUM()

Функция COUNT() возвращает количество строк, соответствующих заданному критерию.

Функция AVG() возвращает среднее значение числового столбца.

Функция SUM() возвращает общую сумму числового столбца.

Синтаксис COUNT()

Синтаксис AVG()

Синтаксис SUM()

Демо база данных

Ниже приведен выбор из таблицы «Products» в образце базы данных Northwind:

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18
2	Chang	1	1	24 — 12 oz bottles	19
3	Aniseed Syrup	1	2	12 — 550 ml bottles	10
4	Chef Anton’s Cajun Seasoning	2	2	48 — 6 oz jars	22
5	Chef Anton’s Gumbo Mix	2	2	36 boxes	21.35

Пример COUNT()

Следующая инструкция SQL находит количество продуктов:

Пример

Примечание: Значения NULL не учитываются.

Пример AVG()

Следующая инструкция SQL находит среднюю цену всех продуктов:

Пример

Примечание: Значения NULL игнорируются.

Демо база данных

Ниже приведен выбор из таблицы «OrderDetails» в образце базы данных Northwind:

OrderDetailID	OrderID	ProductID	Quantity
1	10248	11	12
2	10248	42	10
3	10248	72	5
4	10249	14	9
5	10249	51	40

Пример SUM()

Следующая инструкция SQL находит сумму полей «Quantity» в таблице «OrderDetails»:

Пример

Примечание: Значения NULL игнорируются.

Упражнения

Тесты

КАК СДЕЛАТЬ

ПОДЕЛИТЬСЯ

СЕРТИФИКАТЫ

Сообщить об ошибке

Если вы хотите сообщить об ошибке или сделать предложение, не стесняйтесь, присылайте нам электронное письмо:

Ваше предложение:

Спасибо, за вашу помощь!

Ваше сообщение было отправлено в SchoolsW3.

Топ Учебники

Топ Справочники

Топ Примеры

Веб Сертификаты

SchoolsW3 оптимизирован для обучения, тестирования и тренировки. Примеры упрощают и улучшают чтение и базовое понимание. Учебники, справочники, примеры постоянно пересматриваются, для того, чтобы избежать ошибки, невозможно гарантировать правильность всего содержимого. Используя данный сайт, вы соглашаетесь прочитать и принять условия использования, cookie и Политика конфиденциальности. Авторское право 1999 — 2021 Все права защищены.
Работает на W3.CSS.

Источник

Получить среднее значение столбца для групп значений

Помощь в написании контрольных, курсовых и дипломных работ здесь.

Для каждого столбца матрицы найти среднее арифметическое исходных значений элементов
Задание: Дана матрица, состоящая из n строк и m столбцов. Заменить в матрице на 1 каждый.

Для заданной матрицы определить среднее арифметическое значение для каждого столбца
1. Определить среднее арифметическое значение элементов массива F ( n ) , которые удовлетворяют.

Среднее значение групп
Здравствуйте, не могу понять как реализовать запрос. Итак, имеется таблица с id обекта и.

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

Спасибо. Принцип я понял. Помогите найти ошибку..

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

Для произвольной матрицы, элементы столбца с наименьшей суммой увеличить на их среднее значение.
Расчетное задание: Для произвольной матрицы, элементы столбца с наименьшей суммой увеличить на их.

Ограничение значений для столбца множеством значений из другого столбца другой таблицы
Добрый. Не могу нагуглить, допустим есть 2 таблицы: t1 и t2 В t1, допустим, кроме всего прочего.

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

Источник

AVG (Transact-SQL)

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Параллельное хранилище данных

Эта функция возвращает среднее арифметическое группы значений. Значения NULL она не учитывает.

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

ALL
Применяет агрегатную функцию ко всем значениям. ALL является параметром по умолчанию.

DISTINCT
Указывает на то, что функция AVG выполняется только для одного уникального экземпляра каждого значения, независимо от того, сколько раз встречается это значение.

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

OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым применяется функция. Если этот параметр не указан, функция обрабатывает все строки результирующего набора запроса как отдельные группы. order_by_clause определяет логический порядок, в котором выполняется операция. Аргумент order_by_clause является обязательным. Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).

Типы возвращаемых данных

Тип возвращаемого значения определяется типом вычисленного результата выражения.

Результат выражения	Возвращаемый тип
tinyint	int
smallint	int
int	int
bigint	bigint
Категория decimal (p, s)	decimal(38, max(s,6))
Категории money и smallmoney	money
Категории float и real	float

Комментарии

Если тип данных expression является типом данных-псевдонимом, тип возвращаемого значения также является типом данных-псевдонимом. Однако если базовый тип данных типа данных-псевдонима может повышаться, например из tinyint в int, возвращаемое значение будет иметь повышенный тип данных, а не тип данных-псевдоним.

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

AVG — это детерминированная функция, если она используется без предложений OVER и ORDER BY. Она не детерминирована при использовании с предложениями OVER и ORDER BY. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.

Примеры

A. Использование функций SUM и AVG для вычислений

В этом примере вычисляется среднее количество часов отпуска и сумма часов отсутствия по болезни, которые использовали вице-президенты компании Компания Adventure Works Cycles. Каждая из этих агрегатных функций создает одно сводное значение для всех извлеченных строк. В этом примере используется база данных AdventureWorks2012.

Б. Использование функций SUM и AVG в предложении GROUP BY

При использовании с предложением GROUP BY каждая агрегатная функция создает одно значение, охватывающее каждую группу, а не одно значение для всей таблицы. В следующем примере создается итоговое значение для каждой территории сбыта в базе данных AdventureWorks2012. Итог содержит средний бонус, полученный продавцами по каждой территории, и сумму продаж за текущий год для каждой территории.

В. Использование функции AVG с ключевым словом DISTINCT

Эта инструкция возвращает среднюю ориентировочную цену на продукцию из базы данных AdventureWorks2012. При использовании DISTINCT в расчете учитываются только уникальные значения.

Г. Использование функции AVG без ключевого слова DISTINCT

Без ключевого слова DISTINCT функция AVG находит среднюю ориентировочную цену всех продуктов в таблице Product в базе данных AdventureWorks2012, учитывая и все повторяющиеся значения.

Д. Использование предложения OVER

Следующий пример показывает использование функции AVG с предложением OVER для получения скользящего среднего годовых продаж на каждой территории в таблице Sales.SalesPerson в базе данных AdventureWorks2012. Данные секционируются по TerritoryID и логически сортируются по SalesYTD . Это означает, что функция AVG вычисляется для каждой территории на основании объема продаж за год. Обратите внимание, что в TerritoryID 1 для продаж за 2005 год используются две строки, в которых представлены два менеджера по продажам с показателями за этот год. После расчета среднего значения продаж для двух данных строк в вычисление включается третья строка, представляющая продажи за 2006 год.

В этом примере предложение OVER не включает в себя предложение PARTITION BY. Это означает, что функция будет применяться для всех строк, возвращаемых запросом. Предложение ORDER BY, указанное в предложении OVER, определяет логический порядок применения функции AVG. Запрос возвращает скользящее среднее значение продаж за год для всех территорий, указанных в предложении WHERE. Предложение ORDER BY, указанное в инструкции SELECT, определяет порядок, в котором эта инструкция отображает строки запроса.

Источник

SQL — Урок 11. Итоговые функции, вычисляемые столбцы и представления

Теперь, мы хотим узнать, на какую сумму нам привез товар поставщик «Дом печати» (id=2). Составить такой запрос не так просто. Давайте поразмышляем, как его составить:

1. Сначала надо из таблицы Поставки (incoming) выбрать идентификаторы (id_incoming) тех поставок, которые осуществлялись поставщиком «Дом печати» (id=2):

2. Теперь из таблицы Журнал поставок (magazine_incoming) надо выбрать товары (id_product) и их количества (quantity), которые осуществлялись в найденных в пункте 1 поставках. То есть запрос из пункта 1 становится вложенным:

3. Теперь нам надо добавить в результирующую таблицу цены на найденные товары, которые хранятся в таблице Цены (prices). То есть нам понадобится объединение таблиц Журнал поставок (magazine_incoming) и Цены (prices) по столбцу id_product:

4. В получившейся таблице явно не хватает столбца Сумма, то есть вычисляемого столбца . Возможность создания таких столбцов предусмотрена в MySQL. Для этого надо лишь указать в запросе имя вычисляемого столбца и что он должен вычислять. В нашем примере такой столбец будет называться summa, а вычислять он будет произведение столбцов quantity и price. Название нового столбца отделяется словом AS:

5. Отлично, нам осталось лишь просуммировать столбец summa и наконец-то узнаем, на какую сумму нам привез товар поставщик «Дом печати». Синтаксис для использования функции SUM() следущий:

Имя столбца нам известно — summa, а вот имени таблицы у нас нет, так как она является результатом запроса. Что же делать? Для таких случаев в MySQL существуют Представления . Представление — это запрос на выборку, которому присваивается уникальное имя и который можно сохранять в базе данных, для последующего использования.

Синтаксис создания представления следующий:

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

Вычисляемые поля (столбцы)

Представления

Синтаксис создания представлений мы уже рассматривали. После создания представлений, их можно использовать так же, как таблицы. То есть выполнять запросы к ним, фильтровать и сортировать данные, объединять одни представления с другими. С одной стороны это очень удобный способ хранения частоприменяемых сложных запросов (как в нашем примере).

Но следует помнить, что представления — это не таблицы, то есть они не хранят данные, а лишь извлекают их из других таблиц. Отсюда, во-первых, при изменении данных в таблицах, результаты представления так же будут меняться. А во-вторых, при запросе к представлению происходит поиск необходимых данных, то есть производительность СУБД снижается. Поэтому злоупотреблять ими не стоит.

Видеоуроки php + mysql

Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.

Источник

Примеры SQL-запросов в MariaDB (MySQL)

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

Простые примеры использования SELECT

> SELECT FROM

[JOIN ] [ WHERE ORDER BY LIMIT ]

* где fields1 — поля для выборки через запятую, также можно указать все поля знаком *; table — имя таблицы, из которой вытаскиваем данные; conditions — условия выборки; fields2 — поле или поля через запятую, по которым выполнить сортировку; count — количество строк для выгрузки.
* запрос в квадратных скобках не является обязательным для выборки данных.

1. Обычная выборка данных

> SELECT * FROM users

* в данном примере мы получаем список всех записей из таблицы users.

2. Выборка данных с объединением двух таблиц (JOIN)

SELECT u.name, r.* FROM users u JOIN users_rights r ON r.user_id=u.id

* в данном примере идет выборка данных с объединением таблиц users и users_rights. Объединяются они по полям user_id (в таблице users_rights) и id (users). Извлекается поле name из первой таблицы и все поля из второй.

3. Выборка с интервалом по времени и/или дате

а) известна точка начала и определенный временной интервал:

> SELECT * FROM users WHERE date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)

* будут выбраны данные за последний час (поле date).

б) известны дата начала и дата окончания:

> SELECT * FROM users WHERE date >= ‘2017-10-25’ AND date SELECT * FROM users WHERE DATE(date) BETWEEN ‘2018-03-25 00:15:00’ AND ‘2018-04-25 15:33:09’;

* выбираем данные в промежутке между 25.03.2018 0 часов 15 минут и 25.04.2018 15 часов 33 минуты и 9 секунд.

г) вытаскиваем данные за определенные месяц и год:

> SELECT * FROM study WHERE MONTH(date) = 4 AND YEAR(date) = 2018

* извлечем данные, где в поле date присутствуют значения для апреля 2018 года.

4. Выборка максимального, минимального и среднего значения

> SELECT max(area), min(area), avg(area) FROM country

* max — максимальное значение; min — минимальное; avg — среднее.

5. Использование длины строки

> SELECT * FROM users WHERE CHAR_LENGTH(name) = 5;

* данный запрос должен показать всех пользователей, имя которых состоит из 5 символов.

6. Использование лимитов (LIMIT)

Применяется для ограничения количества выводимых результатов. Синтаксис:

* где число1 — сколько результатов вернуть; число2 — сколько результатов пропустить, необязательный параметр — если его не писать, то отсчет начнется с первой строки.

а) извлечь максимум 15 строк:

> SELECT * FROM users LIMIT 15;

б) выбрать строки с 16 по 25 (запрос со смещением):

> SELECT * FROM users LIMIT 15, 10;

* 15 строк пропускаем, 10 извлекаем.

Примеры более сложных запросов или используемых редко

1. Объединение с группировкой выбранных данных в одну строку (GROUP_CONCAT)

> SELECT GROUP_CONCAT(DISTINCT CONVERT(id USING ‘utf8’) SEPARATOR ‘, ‘) as ids FROM users

* из таблицы users извлекаются данные по полю id, все они помещаются в одну строку, значения разделяются запятыми.

2. Группировка данных по двум и более полям

> SELECT * FROM users GROUP BY CONCAT(title, ‘::’, birth)

* итого, в данном примере мы сделаем выгрузку данных из таблицы users и сгруппируем их по полям title и birth. Перед группировкой мы делаем объединение полей в одну строку с разделителем ::.

3. Объединение результатов из двух таблиц (UNION)

> (SELECT id, fio, address, ‘Пользователи’ as type FROM users)
UNION
(SELECT id, fio, address, ‘Покупатели’ as type FROM customers)

* в данном примере идет выборка данных из таблиц users и customers.

4. Выборка средних значений, сгруппированных за каждый час

SELECT avg(temperature), DATE_FORMAT(datetimeupdate, ‘%Y-%m-%d %H’) as hour_datetime FROM archive GROUP BY DATE_FORMAT(datetimeupdate, ‘%Y-%m-%d %H’)

* здесь мы извлекаем среднее значение поля temperature из таблицы archive и группируем по полю datetimeupdate (с разделением времени за каждый час).

5. Использование операторов IF и CASE

Данные операторы позволяют определять исход запроса исходя из условия.

а) выбрать пол мужской или женский:

SELECT IF(sex = ‘m’, ‘мужчина’, ‘женщина’) as sex FROM people

* в данном примере мы возвращаем слово «мужчина», если поле sex равно ‘m‘, иначе — «женщина».

б) заменяем идентификатор времени года более понятным человеку значением:

SELECT CASE season_id WHEN 1 THEN ‘зима’ WHEN 2 THEN ‘весна’ WHEN 3 THEN ‘лето’ WHEN 4 THEN ‘осень’ ELSE ‘неправильный идентификатор времени года’ END as season FROM ` seasons

* в данном примере мы используем оператор CASE. Если 1, то вернем слово «зима», если 2 — «весна» и так далее.

Вставка (INSERT)

> INSERT INTO

( ) VALUES ( )

> INSERT INTO

VALUES ( )

* где table — имя таблицы, в которую заносим данные; fields — перечисление полей через запятую; values — перечисление значений через запятую.
* первый вариант позволит сделать вставку только по перечисленным полям — остальные получат значения по умолчанию. Второй вариант потребует вставки для всех полей.

1. Вставка нескольких строк одним запросом:

> INSERT INTO cities (`name`, `country`) VALUES (‘Москва’, ‘Россия’), (‘Париж’, ‘Франция’), (‘Фунафути’ ,’Тувалу’);

* в данном примере мы одним SQL-запросом добавим 3 записи.

2. Вставка из другой таблицы (копирование строк, INSERT + SELECT):

Синтаксис при копировании строк из одной таблицы в другую выглядит так:

> INSERT INTO SELECT * FROM WHERE ;

* где table1 — куда копируем; table2 — откуда копируем.

а) скопировать все без разбора:

> INSERT INTO cities-new SELECT * FROM cities;

* в данном примере мы скопируем все строки из таблицы cities в таблицу cities-new.

б) скопировать определенные столбцы строк с условием:

> INSERT INTO cities-new (`name`, `country`) SELECT `name`, `country` FROM cities WHERE name LIKE ‘М%’;

* извлекаем все записи из таблицы cities, названия которых начинаются на «М» и заносим в таблицу cities-new.

в) копирование с обновлением повторяющихся ключей.

Если копировать таблицы несколько раз, то может возникнуть проблема повторения первичного ключа. В базах данных значения таких ключей должны быть уникальными и при попытке вставить повтор мы получим ошибку «Duplicate entry ‘xxx’ for key ‘PRIMARY’». Чтобы новые строки вставить, а повторяющиеся обновить (если есть изменения), используем «ON DUPLICATE KEY UPDATE»:

> INSERT INTO cities-new SELECT * FROM cities ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `country`=VALUES(`country`);

* в данном примере, как и в предыдущих, мы копируем данные из таблицы cities в таблицу cities-new. Но при совпадении значений первичного ключа мы будем обновлять поля name и country.

Обновление (UPDATE)

> UPDATE

SET =’ ‘ WHERE

* где table — имя таблицы; field — поле, для которого будем менять значение; value — новое значение; conditions — условие (без него делать update опасно — можно заменить все данные во всей таблице).

Обновление с использованием замены (REPLACE):

UPDATE

SET = REPLACE( , ‘ ‘, ‘ ‘);

UPDATE cities SET name = REPLACE(name, ‘Масква’, ‘Москва’);

UPDATE cities SET name = REPLACE(name, ‘Масква’, ‘Москва’) WHERE country = ‘Россия’;

UPDATE cities SET name = REPLACE(name, ‘Ма’, ‘Мо’) WHERE name = ‘Масква’;

Если мы хотим перестраховаться, результат замены можно сначала проверить с помощью SELECT:

SELECT REPLACE(name, ‘Ма’, ‘Мо’) FROM cities WHERE name = ‘Масква’;

Удаление (DELETE)

> DELETE FROM

WHERE

* где table — имя таблицы; conditions — условие (как и в случае с UPDATE, использовать DELETE без условия опасно — СУБД не запросит подтверждения, а просто удалит все данные).

Более сложный вариант — удаление данных с объединением таблиц. Запрос будет такого вида:

> DELETE u FROM users u JOIN users_rights r ON r.user_id=u.id WHERE r.admin=’1′

* в данном примере мы удалим записи только из таблицы users (u), которые при объединении с таблицей users_rights будут соответствовать условию r.admin=’1′.

Создание таблицы

> CREATE TABLE

( , )

> CREATE TABLE IF NOT EXISTS `users_rights` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`rights` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

* где table — имя таблицы (в примере users_rights); field1, field2 — имя полей (в примере создается 3 поля — id, user_id, rights); options1, options2 — параметры поля (в примере int(10) unsigned NOT NULL); table options — общие параметры таблицы (в примере ENGINE=InnoDB DEFAULT CHARSET=utf8).

Использование запросов в PHP

Подключаемся к базе данных:

mysql_connect (‘localhost’, ‘login’, ‘password’) or die («MySQL connect error»);
mysql_select_db (‘db_name’);
mysql_query(«SET NAMES ‘utf8′»);

* где подключение выполняется к базе на локальном сервере (localhost); учетные данные для подключения — login и password (соответственно, логин и пароль); в качестве базы используется db_name; используемая кодировка UTF-8.

Также можно создать постоянное подключение:

mysql_pconnect (‘localhost’, ‘login’, ‘password’) or die («MySQL connect error»);

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

* в PHP выполняется автоматически, кроме постоянных подключений (mysql_pconnect).

Запрос к MySQL (Mariadb) в PHP делается функцией mysql_query(), а извлечение данных из запроса — mysql_fetch_array():

$result = mysql_query(«SELECT * FROM users»);
while ($mass = mysql_fetch_array($result)) <
echo $mass[name] . ‘
‘;
>

* в данном примере выполнен запрос к таблице users. Результат запроса помещен в переменную $result. Далее используется цикл while, каждая итерация которого извлекает массив данных и помещает его в переменную $mass — в каждой итерации мы работаем с одной строкой базы данных.

Используемая функция mysql_fetch_array() возвращает ассоциативный массив, с которым удобно работать, но есть еще альтернатива — mysql_fetch_row(), которая возвращает обычный нумерованный массив.

Экранирование

При необходимости включения в строку запроса спецсимвола, например, %, необходимо использовать экранирование с помощью символа обратного слэша — \

> SELECT * FROM producrions WHERE kpd = ‘100\%’

* если выполнить такой запрос без экранирования, знак %, будет восприниматься как любое количество символов после 100.

Использование переменных

Пременные задаются с помощью знака собаки, например:

> SET @number = 101;

* мы создали переменную number со значением 101.

Теперь можно применить переменную в запросе, например:

> INSERT INTO users (`user_number`, `user_name`) VALUES (@number, CONCAT(‘Пользователь ‘, @number));

Источник