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

Условие поиска раздела HAVING строится по тем же синтаксическим правилам, что и условие поиска раздела WHERE, и может включать те же самые предикаты.

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

Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.

Пусть запрос вида (в качестве базовой таблицы см. рис. 4.22):

SELECT Ид_сотр, Дата, MAX ((Сумма))

FROM Ведомость1

GROUP BY Ид_сотр, Дата;

необходимо уточнить тем, чтобы были показаны только выплаты, превышающие 1000.

Однако по стандарту агрегатную функцию в предложении WHERE использовать запрещено (если вы не используете подзапрос, описанный позже), потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции оцениваются в терминах группы строк.

Следующее предложение будет неправильным:

SELECT Ид_сотр, Дата, MAX (Сумма)

FROM Ведомость1

WHERE MAX ((Сумма)) > 1000 GROUP BY Ид_сотр, Дата;

Правильным предложением будет:

SELECT Ид_сотр, Дата, MAX ((Сумма))

Если в табличном выражении присутствует раздел GROUP BY SQL , то следующим выполняется GROUP BY .

Если обозначить через R таблицу, являющуюся результатом предыдущего раздела (FROM или WHERE ), то результатом раздела GROUP BY является разбиение R на множество групп строк, состоящего из минимального числа групп таких, что для каждого столбца из списка столбцов раздела GROUP BY во всех строках каждой группы, включающей более одной строки, значения этого столбца равны. Для обозначения результата раздела GROUP BY в стандарте используется термин “сгруппированная таблица ”.

Если утверждение SELECT содержит предложение GROUP BY (SELECT GROUP BY ), список выбора может содержать только следующие типы выражений :

• Константы .
• Агрегатные функции .
• Функции USER, UID, и SYSDATE.
• Выражения , соответствующие перечисленным в предложении GROUP BY .
• Выражения , включающие вышеперечисленные выражения.

Пример 1. Вычислить общий объем покупок для каждого товара:

SELECT stock, SUM(quant) FROM ordsale GROUP BY stock;

Фраза GROUP BY не предполагает упорядочивания строк. Для упорядочивания результата этого примера по кодам товаров, следует поместить фразу ORDER BY stock следом за фразой GROUP BY.

Пример 2. Можно использовать группировки данных GROUP BY совместно с условием. Например, выбрать для каждого покупаемого товара его код и общий объем покупок, за исключением покупок покупателя с кодом 23:

SELECT stock, SUM(quant) FROM ordsale WHERE customerno<>23 GROUP BY stock;

Строки, не удовлетворяющие условию WHERE , исключаются перед группированием данных.

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

Допустим, есть задача на вычисление количества какого-либо продукта. Поставщик поставляет нам продукцию по определённой цене. Вычислим общее количество каждого из продуктов. В этом нам поможет фраза GROUP BY. Результатом задачи станет таблица, состоящая из нескольких колонок. Поставки будут группироваться по ПР. Компоновка происходит по группам, которую и инициирует Group By SQL. Необходимо отметить, что данная фраза предполагает применение фразы Select, она же в свою очередь определяет единственное значение для каждого выражения сформированной группы. Бывают три случая для конкретного выражения: оно принимает арифметическое значение, оно становится SQL-функцией, которая будет сводить все значения столбца к сумме или другому заданному значению, также выражение может стать константой. Строки таблицы не обязательно должны быть строго сгруппированы, они могут группироваться по любой комбинации столбцов таблицы. Необходимо учитывать, что упорядочивание запросы по ПР возможно в том случае, если будет сделан соответствующий запрос.

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

Описание

SQL оператор GROUP BY можно использовать в операторе SELECT для сбора данных по нескольким записям и группировки результатов по одному или нескольким столбцам.

Синтаксис

Синтаксис оператора GROUP BY в SQL:

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

Пример — использование GROUP BY с функцией SUM

Давайте посмотрим, как использовать GROUP BY с функцией SUM в SQL.
В этом примере у нас есть таблица employees со следующими данными:

В этом примере мы использовали функцию SUM, чтобы сложить все зарплаты для каждого dept_id , и мы результатам SUM(salary) указали псевдоним «total_salaries». Поскольку dept_id не инкапсулирован в функцию SUM, он должен быть указан в предложении GROUP BY.

Пример — использование GROUP BY с функцией COUNT

Давайте посмотрим, как использовать предложение GROUP BY с функцией COUNT в SQL.

В этом примере у нас есть таблица products со следующими данными:

В этом примере мы использовали функцию COUNT для вычисления количества total_products для каждого category_id , и мы указали псевдоним «total_products» как результаты функции COUNT. Мы исключили все значения category_id , которые имеют значение NULL, отфильтровав их в предложении WHERE. Поскольку category_id не инкапсулирован в функции COUNT, он должен быть указан в предложении GROUP BY.

Пример — использование GROUP BY с функцией MIN

Давайте теперь посмотрим, как использовать предложение GROUP BY с функцией MIN в SQL.

В этом примере мы снова будем использовать таблицу employees со следующими данными:

Будет выбрано 2 записи. Вот результаты, которые вы получите:

В этом примере мы использовали функцию MIN, чтобы вернуть самое минимальное значение salary для каждого dept_id , и мы присвоили результатам функции MIN псевдоним «lowest_salary». Поскольку dept_id не инкапсулирован в функцию MIN, он должен быть указан в предложении GROUP BY.

Пример — использование GROUP BY с функцией MAX

Наконец, давайте посмотрим, как использовать предложение GROUP BY с функцией MAX.

Давайте снова воспользуемся таблицей employees , но на этот раз найдем самую максимальную зарплату для каждого dept_id :

Введите следующий SQL оператор.

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

1. Создание групп (GROUP BY)

Группы создаются с помощью предложения GROUP BY оператора SELECT . Рассмотрим на примере.

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product

Данным запросом мы извлекли информацию о количестве реализованной продукции в каждом месяце. Оператор SELECT приказывает вывести два столбца Product - название продукта и Product_num - расчетное поле, которое мы создали для отображения количества реализованной продукции (формула поля SUM (Quantity)). Предложение GROUP BY указывает СУБД сгруппировать данные по столбцу Product . Стоит также отметить, что GROUP BY должен идти после предложения WHERE и перед ORDER BY .

2. Фильтрующие группы (HAVING)

Так же, как мы фильтровали строки в таблице, мы можем осуществлять фильтрацию по сгруппированным данным. Для этого в SQL существует оператор HAVING . Возьмем предыдущий пример и добавим фильтрацию по группам.

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>4000

Видим, что после того, как была посчитана количество реализованного товара в разрезе каждого продукта, СУБД "отсекла" те продукты, которых было реализовано меньше 4000 шт.

Как видим, оператор HAVING очень похож на оператора WHERE , однако между собой они имеют существенное отличие: WHERE фильтрует данные до того, как они будут сгруппированы, а HAVING - осуществляет фильтрацию после группировки. Таким образом, строки, которые были изъяты предложением WHERE НЕ будут включены в группу. Итак, операторы WHERE и HAVING могут использоваться в одном предложении. Рассмотрим пример:

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct WHERE Product<>"Skis Long" GROUP BY Product HAVING SUM(Quantity)>4000

Мы к предыдущему примеру добавили оператор WHERE , где указали товар Skis Long , что в свою очередь повлияло на группирование оператором HAVING . Как результат видим, что товар Skis Long не попал в перечень групп с количеством реализованной продукции больше 4000 шт.

3. Группировка и сортировка

Как и при обычной выборке данных, мы можем сортировать группы после группировки оператором HAVING . Для этого мы можем использовать уже знакомый нам оператор ORDER BY . В данной ситуации его применения аналогичное предыдущим примерам. К примеру:

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>3000 ORDER BY SUM(Quantity)

или просто укажем номер поля по порядку, по которому хотим сортировать:

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>3000 ORDER BY 2

Видим, что для сортировки сводных результатов нам нужно просто прописать предложения с ORDER BY после оператора HAVING . Однако есть один нюанс. СУБД Access не поддерживает сортировку групп по псевдонимами колонок, то есть в нашем примере, чтобы сортировать значения, мы не сможем в конце запроса прописать ORDER BY Product_num .

В рамках данной статьи я расскажу вам о том, как осуществляется группировка данных, как правильно применять group by и having внутри SQL-запросов на примере нескольких запросов.

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

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

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

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

user_name - имя пользователя

forum_group - имя группы

mess_count - количество сообщений

is_have_social_profile - указан ли в профиле форуме ссылка на страничку в социальной сети

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

Чистая группировка с помощью group by

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

Вначале, небольшое словесное описание, чтобы легче было понимать SQL-запрос. Итак, вам нужно найти вычисляемые значения по группам форума. Соответственно, вам нужно поделить все эти десять строк на три разные группы: admin, moder, user. Чтобы это сделать, нужно в конце запроса добавить группировку по значениям поля forum_group. А так же добавить в select вычисляемые выражения с использованием так называемых агрегатных функций.

Указываем поля и вычисляемые столбцы select forum_group, avg(raiting) as avg_raiting, sum(mess_count) as total_mess_count -- Указываем таблицу from userstat -- Указываем группировку по полю group by forum_group

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

Так же я воспользовался двумя агрегатными функциями. AVG - вычисляет среднее значение. И SUM - вычисляет сумму.

1. Вначале все строки исходной таблицы были разбиты на три группы по значениям поля forum_group. Например, внутри группы admin было три пользователя. Внутри moder так же 3 строки. А внутри группы user было 4 строки (четыре пользователя).

2. Затем для каждой группы применялись агрегатные функции. Например, для группы admin средний рейтинг вычислялся так (2 + 5 + 5)/3 = 4. Количество сообщений вычислялось так (10 + 15 + 25) = 50.

Как видите, ничего сложного. Однако, мы применили всего одно условие группировки и не применяли фильтрацию по группам. Поэтому перейдем к следующему примеру.

Группировка с помощью group by и фильтрацией групп с having

Теперь, рассмотрим более сложный пример группировки данных. Допустим нам нужно оценить эффективность действий по привлечению пользователей к социальной деятельности. Если по простому, то узнать сколько пользователей в группах оставило ссылки на свои профили, а сколько проигнорировало рассылки и прочее. Однако, в реальной жизни таких групп может быть много, поэтому нам нужно отфильтровать те группы, которыми можно пренебречь (к примеру, слишком мало людей не оставило ссылку; зачем захламлять полный отчет). В моем примере это группы, где всего один пользователь.

Вначале, словесно опишем что необходимо сделать в SQL-запросе. Нам нужно все строки исходной таблицы userstat разделить по следующим признакам: имя группы и наличие социального профиля. Соответственно, необходимо группировать данные таблицы по полям forum_group и is_have_social_profile. Однако, нас не интересуют те группы, где всего один человек. Следовательно такие группы нужно отфильтровать.

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

Так же хотел бы сразу уточнить один важный момент. Фильтровать с помощью having можно только при применении агрегатных функций, а не по отдельным полям. Другими словами, это не конструкция where, это фильтр именно групп строк, а не отдельных записей. Хотя условия внутри задаются аналогичным образом с помощью "or" и "and".

Вот как будет выглядеть SQL-запрос

Указываем поля и вычисляемые столбцы select forum_group, is_have_social_profile, count(*) as total -- Указываем таблицу from userstat -- Указываем группировку по полям group by forum_group, is_have_social_profile -- Указываем фильтр групп having count(*) > 1

Обратите внимание, что поля после конструкции group by указываются через запятую. Указание полей в select происходит аналогичным образом, как и в предыдущем примере. Так же я воспользовался агрегатной функцией count, которая вычисляет количество строк в группах.

Вот какой получился результат:

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

1. Вначале было получено 6 групп. Каждая из групп по forum_group была разбита на две подгруппы по значениям поля is_have_social_profile. Другими словами группы: , , , , , .

Примечание : Кстати, групп не обязательно должно было бы получится 6. Так, к примеру, если бы все администраторы заполнили профиль, то групп было бы 5, так как поле is_have_social_profile имело бы только одно значение у пользователей группы admin.

2. Затем для каждой группы было применено условие фильтрации в having. Поэтому были исключены следующие группы: , , . Так как внутри каждой такой группы присутствовала всего одна строка исходной таблицы.

3. После этого были вычислены необходимые данные и был получен результат.

Как видите, ничего сложно в использовании нет.

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

Теперь, вы знаете как применять группировку данных с group by, а так же как фильтровать группы с помощью having.