Sql запросы count. Посчитать количество записей в группах (созданных GROUP BY)
Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.
Функция SQL SUM
Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:
SELECT SUM (ИМЯ_СТОЛБЦА) ...
После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).
Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):
Для получения суммы размеров всех заработных плат используем следующий запрос:
SELECT SUM (Salary) FROM Staff
Этот запрос вернёт значение 287664,63.
А теперь . В упражнениях уже начинаем усложнять задания, приближая их к тем, что встречаются на практике.
Функция SQL MIN
Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.
Пример 3. База данных и таблица - те же, что и в примере 1.
Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Запрос вернёт значение 10505,90.
И вновь упражнение для самостоятельного решения . В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:
Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.
Функция SQL MAX
Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.
Пример 5.
Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Запрос вернёт значение 18352,80
Пришло время упражнения для самостоятельного решения .
Пример 6. Вновь работаем с двумя таблицами - Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц) .
Функция SQL AVG
Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG. Эта функция возвращает среднее значение среди всех значений столбца.
Пример 7. База данных и таблица - те же, что и в предыдущих примерах.
Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Результатом будет значение 6,33
Пример 8. Работаем с одной таблицей - Staff. Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет.
Функция SQL COUNT
Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать SELECT COUNT(ИМЯ_СТОЛБЦА) ..., то результатом будет количество записей без учёта тех записей, в которых значением столбца является NULL (неопределённое). Если использовать в качестве аргумента звёздочку и начать запрос SELECT COUNT(*) ..., то результатом будет количество всех записей (строк) таблицы.
Пример 9. База данных и таблица - те же, что и в предыдущих примерах.
Требуется узнать число всех сотрудников, которые получают комиссионные. Число сотрудников, у которых значения столбца Comm - не NULL, вернёт следующий запрос:
SELECT COUNT (Comm) FROM Staff
Результатом будет значение 11.
Пример 10. База данных и таблица - те же, что и в предыдущих примерах.
Если требуется узнать общее количество записей в таблице, то применяем запрос со звёздочкой в качестве аргумента функции COUNT:
SELECT COUNT (*) FROM Staff
Результатом будет значение 17.
В следующем упражнении для самостоятельного решения потребуется использовать подзапрос.
Пример 11. Работаем с одной таблицей - Staff. Вывести число сотрудников в отделе планирования (Plains).
Агрегатные функции вместе с SQL GROUP BY (группировкой)
Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY служит для группировки результирующих значений по столбцам таблицы базы данных. На сайте есть урок, посвящённый отдельно этому оператору .
Пример 12. Есть база данных портала объявлений. В ней есть таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит данные о больших категориях объявлений (например, Недвижимость), а столбец Parts - о более мелких частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость). Столбец Units содержит данные о количестве поданных объявлений, а столбец Money - о денежных суммах, вырученных за подачу объявлений.
| Category | Part | Units | Money |
| Транспорт | Автомашины | 110 | 17600 |
| Недвижимость | Квартиры | 89 | 18690 |
| Недвижимость | Дачи | 57 | 11970 |
| Транспорт | Мотоциклы | 131 | 20960 |
| Стройматериалы | Доски | 68 | 7140 |
| Электротехника | Телевизоры | 127 | 8255 |
| Электротехника | Холодильники | 137 | 8905 |
| Стройматериалы | Регипс | 112 | 11760 |
| Досуг | Книги | 96 | 6240 |
| Недвижимость | Дома | 47 | 9870 |
| Досуг | Музыка | 117 | 7605 |
| Досуг | Игры | 41 | 2665 |
Используя оператор SQL GROUP BY, найти суммы денег, вырученных за подачу объявлений в каждой категории. Пишем следующий запрос:
SELECT Category, SUM (Money) AS Money FROM Ads GROUP BY Category
Пример 13. База данных и таблица - та же, что в предыдущем примере.
Используя оператор SQL GROUP BY, выяснить, в какой части каждой категории было подано наибольшее число объявлений. Пишем следующий запрос:
SELECT Category, Part, MAX (Units) AS Maximum FROM Ads GROUP BY Category
Результатом будет следующая таблица:
Итоговые и индивидуальные значения в одной таблице можно получить объединением результатов запросов с помощью оператора UNION .
Реляционные базы данных и язык SQL
Чтобы определить количество записей в таблице 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() на примере
Вот еще один пример использования функции COUNT() . Допустим, есть таблица ice_cream с каталогом мороженого, в которой находятся идентификаторы категорий и названия мороженого.
Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций . Стандартом предусмотрены следующие агрегатные функции:
Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT(<имя поля>) состоит в том, что вторая при подсчете не учитывает NULL-значения.
Пример. Найти минимальную и максимальную цену на персональные компьютеры:
Пример. Найти имеющееся в наличии количество компьютеров, выпущенных производителем А:
Пример. Если же нас интересует количество различных моделей, выпускаемых производителем А, то запрос можно сформулировать следующим образом (пользуясь тем фактом, что в таблице Product каждая модель записывается один раз):
Пример. Найти количество имеющихся различных моделей, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC (т.е. имеющихся в продаже).
Для того, чтобы при получении статистических показателей использовались только уникальные значения, при аргументе агрегатных функций можно использовать параметр DISTINCT . Другой параметр ALL используется по умолчанию и предполагает подсчет всех возвращаемых значений в столбце. Оператор,
Если же нам требуется получить количество моделей ПК, производимых каждым производителем, то потребуется использовать предложение GROUP BY , синтаксически следующего после предложения WHERE .
Предложение GROUP BY
Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM) . Если это предложение отсутствует, и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT , должны быть включены в агрегатные функции , и эти функции будут применяться ко всему набору строк, которые удовлетворяют предикату запроса. В противном случае все столбцы списка SELECT, не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY . В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После этого к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу.Если при наличии предложения GROUP BY , в предложении SELECT отсутствуют агрегатные функции , то запрос просто вернет по одной строке из каждой группы. Эту возможность, наряду с ключевым словом DISTINCT, можно использовать для исключения дубликатов строк в результирующем наборе.
Рассмотрим простой пример:
| SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model; |
В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT вычисляются количество значений и средние значения цены для каждой группы. Результатом выполнения запроса будет следующая таблица:
| model | Qty_model | Avg_price |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений (модель−дата).
Существует несколько определенных правил выполнения агрегатных функций :
- Если в результате выполнения запроса не получено ни одной строки (или не одной строки для данной группы), то исходные данные для вычисления любой из агрегатных функций отсутствуют. В этом случае результатом выполнения функций COUNT будет нуль, а результатом всех других функций - NULL.
- Аргумент агрегатной функции не может сам содержать агрегатные функции (функция от функции). Т.е. в одном запросе нельзя, скажем, получить максимум средних значений.
- Результат выполнения функции COUNT есть целое число (INTEGER). Другие агрегатные функции наследуют типы данных обрабатываемых значений.
- Если при выполнении функции SUM был получен результат, превышающий максимальное значение используемого типа данных, возникает ошибка .
Итак, если запрос не содержит предложения GROUP BY , то агрегатные функции , включенные в предложение SELECT , исполняются над всеми результирующими строками запроса. Если запрос содержит предложение GROUP BY , каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY , составляет группу, и агрегатные функции выполняются для каждой группы отдельно.
Предложение HAVING
Если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций . Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк источника записей, определенного в предложении FROM , а из групп таких строк . Поэтому такая проверка не может содержаться в предложении WHERE .
Начиная от версии 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 перечислены ниже:
- Ключевое слово SQL_CALC_FOUND_ROWS должно указываться в первом операторе SELECT.
- Значение FOUND_ROWS() будет точным только при условии применения UNION ALL. Если указано UNION без ALL, происходит исключение дубликатов, и значение FOUND_ROWS() будет лишь приблизительным.
- Если в UNION не присутствует LIMIT, то SQL_CALC_FOUND_ROWS игнорируется и возвращается количество строк во временной таблице, которая создается для выполнения UNION.
SQL - Урок 8. Группировка записей и функция COUNT()
Давайте вспомним, какие сообщения и в каких темах у нас имеются. Для этого можно воспользоваться привычным запросом:
А что, если нам надо лишь узнать сколько сообщений на форуме имеется. Для этого можно воспользоваться встроенной функцией 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 можно использовать и другие встроенные функции, но их мы будем изучать позже.