Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций . Стандартом предусмотрены следующие агрегатные функции:
Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT(<имя поля>) состоит в том, что вторая при подсчете не учитывает NULL-значения.
Пример. Найти минимальную и максимальную цену на персональные компьютеры:
Пример. Найти имеющееся в наличии количество компьютеров, выпущенных производителем А:
Пример. Если же нас интересует количество различных моделей, выпускаемых производителем А, то запрос можно сформулировать следующим образом (пользуясь тем фактом, что в таблице Product каждая модель записывается один раз):
Пример. Найти количество имеющихся различных моделей, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC (т.е. имеющихся в продаже).
Для того, чтобы при получении статистических показателей использовались только уникальные значения, при аргументе агрегатных функций можно использовать параметр DISTINCT . Другой параметр ALL используется по умолчанию и предполагает подсчет всех возвращаемых значений в столбце. Оператор,
Если же нам требуется получить количество моделей ПК, производимых каждым производителем, то потребуется использовать предложение GROUP BY , синтаксически следующего после предложения WHERE .
| SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model; |
| model | Qty_model | Avg_price |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений (модель−дата).
Существует несколько определенных правил выполнения агрегатных функций :
Итак, если запрос не содержит предложения GROUP BY , то агрегатные функции , включенные в предложение SELECT , исполняются над всеми результирующими строками запроса. Если запрос содержит предложение GROUP BY , каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY , составляет группу, и агрегатные функции выполняются для каждой группы отдельно.
Если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций . Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк источника записей, определенного в предложении FROM , а из групп таких строк . Поэтому такая проверка не может содержаться в предложении WHERE .
В этом учебном пособии вы узнаете, как использовать функцию COUNT
в SQL Server (Transact-SQL) с синтаксисом и примерами.
В SQL Server (Transact-SQL) функция COUNT возвращает количество строк поля или выражения в результирующий набор.
Синтаксис функции COUNT в SQL Server (Transact-SQL):
ИЛИ синтаксис функции COUNT при группировке результатов одного или нескольких столбцов:
expression1
, expression2
, … expression_n
Выражения, которые не заключены в функцию COUNT и должны быть включены в предложение GROUP BY в конце SQL предложения.
aggregate_expression
— это столбец или выражение, чьи не NULL значения будут подсчитаны.
tables
— таблицы, из которых вы хотите получить записи. Должна быть хотя бы одна таблица, перечисленная в операторе FROM.
WHERE conditions
— необязательный. Это условия, которые должны выполняться для выбранных записей.
Не все это понимают, но функция COUNT будет считать только те записи, где значение выражения в COUNT (aggregate_expression ) не равно NULL. Когда выражение содержит значение NULL, оно не включается в счетчик COUNT.
Рассмотрим пример функции COUNT, который демонстрирует, как значения NULL оцениваются функцией COUNT.
Например, если у вас есть следующая таблица, называемая markets :
Этот пример COUNT вернет 3, так как все значения market_id в наборе результатов запроса НЕ являются NULL.
Однако, если вы запустили следующий оператор SELECT, который использует функцию COUNT:
Transact-SQL
SELECT COUNT(filials) FROM markets; --Результат: 1
Этот пример COUNT будет возвращать только 1, так как только одно значение filials в наборе результатов запроса НЕ NULL. Это будет первая строка, где будет указано filials = «yes». Это единственная строка, которая включена в вычисление функции COUNT.
Функция COUNT может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Рассмотрим некоторые примеры SQL Server функции COUNT, чтобы понять, как использовать функцию COUNT в SQL Server (Transact-SQL).
Например, вы можете узнать, сколько contacts имеет пользователь с last_name = «Rasputin».
В этом примере функции COUNT мы выражению COUNT (*) указали алиас «Number of contacts». Поэтому в результирующем наборе будет отображаться «Number of contacts» в качестве имени поля.
Вы можете использовать оператор DISTINCT в функции COUNT. Например, приведенный ниже оператор SQL возвращает число уникальных department , где хотя бы один сотрудник имеет first_name = ‘Samvel’.
А что, если нам надо лишь узнать сколько сообщений на форуме имеется. Для этого можно воспользоваться встроенной функцией COUNT() . Эта функция подсчитывает число строк. Причем, если в качестве аргумента этой функции выступает *, то подсчитываются все строки таблицы. А если в качестве аргумента указывается имя столбца, то подсчитываются только те строки, которые имеют значение в указанном столбце.
В нашем примере оба аргумента дадут одинаковый результат, т.к. все столбцы таблицы имеют тип NOT NULL. Давайте напишем запрос, используя в качестве аргумента столбец id_topic:
SELECT COUNT(id_topic) FROM posts;
Итак, в наших темах имеется 4 сообщения. Но что, если мы хотим узнать сколько сообщений имеется в каждой теме. Для этого нам понадобится сгруппировать наши сообщения по темам и вычислить для каждой группы количество сообщений. Для группировки в SQL используется оператор GROUP BY . Наш запрос теперь будет выглядеть так:
SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic;
Оператор GROUP BY указывает СУБД сгруппировать данные по столбцу id_topic (т.е. каждая тема - отдельная группа) и для каждой группы подсчитать количество строк:
Ну вот, в теме с id=1 у нас 3 сообщения, а с id=4 - одно. Кстати, если бы в поле id_topic были возможны отсутствия значений, то такие строки были бы объединены в отдельную группу со значением NULL.
Предположим, что нас интересуют только те группы, в которых больше двух сообщений. В обычном запросе мы указали бы условие с помощью оператора WHERE , но этот оператор умеет работать только со строками, а для групп те же функции выполняет оператор HAVING :
SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic HAVING COUNT(id_topic) > 2;
В результате имеем:
В уроке 4 мы рассматривали, какие условия можно задавать оператором WHERE , те же условия можно задавать и оператором HAVING , только надо запомнить, что WHERE фильтрует строки, а HAVING - группы.
Итак, сегодня мы узнали, как создавать группы и как подсчитать количество строк в таблице и в группах. Вообще вместе с оператором GROUP BY можно использовать и другие встроенные функции, но их мы будем изучать позже.
Начиная от версии 4.0 в СУБД MySQL появилась достаточно удобная возможность подсчета количества всех подходящих под запрос записей, когда количество записей ограничивается LIMIT’ом. При работе с поиском в БД, а так же при выборках из таблиц с большим количеством записей такой функционал просто необходим.
Синтаксис. В запросе SELECT перед списком столбцов необходимо указать опцию SQL_CALC_FOUND_ROWS. Вот начало описания синтаксиса конструкции SELECT.
SELECT
select_expr, … …
Таким образом, выполняя запрос SELECT SQL_CALC_FOUND_ROWS СУБД подсчитает полное число строк, подходящих под условие запроса, и сохранить это число в памяти. Естественно, имеет смысл запрос SELECT SQL_CALC_FOUND_ROWS только при использовании ограничения (LIMIT). Сразу после выполнения запроса на выборку для получения количества записей нужно выполнить еще один SELECT-запрос: SELECT FOUND_ROWS ();. В результате MySQL вернет одну строку с одним полем, в котором и будет храниться число строк.
Пример самих запросов:
> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE number > 100 LIMIT 10;
> SELECT FOUND_ROWS();
Первый запрос вернет (выведет) 10 строк таблицы tbl_name, для которых выполняется условие number > 100. Второй вызов команды SELECT возвратит количество строк, которые возвратила бы первая команда SELECT, если бы она была написана без выражения LIMIT. Хотя при использовании команды SELECT SQL_CALC_FOUND_ROWS, MySQL должен пересчитать все строки в наборе результатов, этот способ все равно быстрее, чем без LIMIT, так как не требуется посылать результат клиенту.
Пример запросов из PHP:
$result = mysql_query(«SELECT SQL_CALC_FOUND_ROWS * FROM table1 LIMIT 0, 10″, $link);
while ($row = mysql_fetch_assoc($result))
{
var_dump($row);
}$result = mysql_query(«SELECT FOUND_ROWS()», $link);
$num_rows = mysql_result($result, 0);
echo «$num_rows Rows\n»;
В результате выполнения кода при условии, что $link указывает на открытое соединение с СУБД, PHP выведет 10 строк из таблицы table1 , а затем целочисленное значение количества строк, соответствующих запросу (без учета LIMIT).
В запросах с UNION SQL_CALC_FOUND_ROWS может вести себя двояко из-за того, что LIMIT может появляться в нескольких местах. Счет строк может вестись для индивидуальных SELECT-запросов, или же для всего запроса после объединения.
Цель SQL_CALC_FOUND_ROWS для UNION состоит в том, что он должен вернуть количество строк, которые будут возвращены без глобального LIMIT. Условия применения SQL_CALC_FOUND_ROWS с UNION перечислены ниже:
Чтобы определить количество записей в таблице MySQL, нужно воспользоваться специальной функцией COUNT().
Функция COUNT() возвращает количество записей в таблице, соответствующих заданному критерию.
Функция COUNT(expr) всегда считает только те строки, у которых результатом выражения expr является NOT NULL .
Исключением из этого правила является использование функции COUNT() со звездочкой в качестве аргумента - COUNT(*) . В этом случае считаются все строки, вне зависимости от того, NULL они или NOT NULL .
Например, функция COUNT(*) возвращает общее количество записей в таблице:
SELECT COUNT(*) FROM table_name
Пример PHP+MySQL-кода для подсчета и вывода общего количества строк:
$res = mysql_query("SELECT COUNT(*) FROM table_name") $row = mysql_fetch_row($res); $total = $row; // всего записей echo $total; ?>
Этот пример иллюстрирует самый простой вариант использования функции COUNT() . Но с помощью этой функции можно выплнять и другие задачи.
Указав определенный столбец таблицы в виде параметра, функция COUNT(column_name) возвращает количество записей этого столбца, которые не содержат значение NULL . Записи со значениями NULL игнорируются.
SELECT COUNT(column_name) FROM table_name
Использовать функцию mysql_num_rows() нельзя, потому что для того, чтобы узнать общее количество записей, нужно выполнить запрос SELECT * FROM db , то есть получить все записи, а это нежелательно, поэтому предпочтительнее использовать функцию count .
$result = mysql_query("SELECT COUNT (*) as rec FROM db");
Вот еще один пример использования функции COUNT() . Допустим, есть таблица ice_cream с каталогом мороженого, в которой находятся идентификаторы категорий и названия мороженого.
