Group by access

SQL-Урок 8. Группировка данных (GROUP BY)

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

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 .

Оператор SQL GROUP BY для группировки в запросах

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

Часто оператор SQL GROUP BY применяется вместе с агрегатными функциями (COUNT, SUM, AVG, MAX, MIN). В этих случаях агрегатные функции служат для вычисления соответствующего агрегатного значения ко всему набору строк, для которых некоторый столбец — общий.

Оператор GROUP BY имеет следующий синтаксис:

Группировка по одному столбцу без агрегатных функций

Если в результате запроса требуется вывести один столбец и по этому же столбцу производится группировка, то оператор GROUP BY просто выбирает уникальные значения и убирает дубликаты, то есть выполняет те же задачи, что и ключевое слово DISTINCT.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными — в файле по этой ссылке .

В примерах работаем с базой данных библиотеки и ее таблицей «Книга в пользовании» (Bookinuse). Отметим, что оператор GROUP BY ведёт себя несколько по-разному в MySQL и в MS SQL Server. Эти различия будут показаны на примерах.

Пример 1. Вывести авторов выданных книг, сгруппировав их. Пишем следующий запрос:

Этот запрос вернёт следующий результат:

Как видим, в таблице стало меньше строк, так как фамилии авторов остались каждая по одной.

В следующем примере увидим, что оператор GROUP BY не следует путать с оператором ORDER BY и поймём, чем эти операторы отличаются друг от друга.

Пример 2. Вывести авторов и названия выданных книг, сгруппировав по авторам. Пишем следующий запрос, который допустим в MySQL:

Этот запрос вернёт следующий результат:

Как видим, в таблице каждому автору соответствует лишь одна книга, причём та, которая в таблице BOOKINUSE является первой по порядку записей.

Если бы нам требовалось вывести все книги, причём авторы должны были бы следовать не «вразброс», а по порядку: сначала Гоголь и все его книги, затем другие авторы и все их книги, то мы применили бы не оператор GROUP BY, а оператор ORDER BY.

По-другому ведёт себя оператор GROUP BY в MS SQL Server. Он требует произвести группировку по обоим столбцам, из которых мы извлекаем данные. Запрос на MS SQL Server будет следующим.

Читателям предлагается самостоятельно выполнить этот запрос и увидеть его результат.

Группировка по нескольким столбцам без агрегатных функций

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

Пример 3. Вывести авторов, названия выданных книг, ID пользователя и инвентарный номер выданной книги. Сгруппировать по авторам, ID пользователя и инвентарному номеру. На MySQL запрос будет следующим:

Этот запрос вернёт следующий результат:

Как видим, в результирующей таблице присутствуют все книги всех авторов, причём авторы следуют по порядку, как если бы мы применили оператор ORDER BY. Кроме того, видно, что записи сгруппированы и по второму указанному столбцу — Customer_ID. Так, у автора Пушкина сначала перечисляются книги, выданные пользователю с Customer_ID 47, а затем — 205. У автора Чехова сначала перечисляются книги, выданные пользователю с Customer_ID 31, а затем — с другими номерами. Третий столбец, по которому происходит группировка — Inv_no — добавлен только для того, чтобы в результирующей таблице выводились все строки, соответствующие значениям ранее перечисленных столбцов для группировки, а не только уникальные.

MS SQL Server вновь ведёт себя иначе и требует, чтобы группировка проводилась по всем столбцам, значения которых мы хотим вывести. Запрос на MS SQL Server следующий (с предваряющей конструкцией USE library;):

Предлагается выполить этот запрос на MS SQL Server и увидеть результат.

Группировка с агрегатными функциями

Агрегатные функции COUNT, SUM, AVG, MAX, MIN служат для вычисления соответствующего агрегатного значения ко всему набору строк, для которых некоторый столбец — общий.

Пример 4. Вывести количество выданных книг каждого автора. Запрос будет следующим:

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

Transact-SQL группировка данных GROUP BY

Мы с Вами рассмотрели много материала по SQL, в частности Transact-SQL, но мы не затрагивали такую, на самом деле простую тему как группировка данных GROUP BY. Поэтому сегодня мы научимся использовать оператор group by для группировки данных.

Многие начинающие программисты, когда сталкиваются с SQL, не знают о такой возможности как группировка данных с помощью оператора GROUP BY, хотя эта возможность требуется достаточно часто на практике, в связи с этим наш сегодняшний урок, как обычно с примерами, посвящен именно тому, чтобы Вам было проще и легче научиться использовать данный оператор, так как Вы с этим обязательно столкнетесь. Если Вам интересна тема SQL, то мы, как я уже сказал ранее, не раз затрагивали ее, например, в статьях Язык SQL – объединение JOIN или Объединение Union и union all , поэтому можете ознакомиться и с этим материалом.

И для вступления небольшая теория.

Что такое оператор GROUP BY

GROUP BY – это оператор (или конструкция, кому как удобней) SQL для группировки данных по полю, при использовании в запросе агрегатных функций, таких как sum, max, min, count и других.

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

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

Примечание! Все примеры будем писать в Management Studio SQL сервера 2008.

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

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

Создаем таблицу

Я ее заполнил следующими данными:

• Id –идентификатор записи;
• Name – фамилия сотрудника;
• Summa- денежные средства;
• Priz – признак денежных средств (допустим 1- Оклад; 2-Премия).

Группируем данные с помощью запроса group by

И в самом начале давайте разберем синтаксис group by, т.е. где писать данную конструкцию:

Синтаксис:

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

Where Условия отбора

Group by поля группировки

Having Условия по агрегатным функциям

Order by поля сортировки

Теперь если нам необходимо просуммировать все денежные средства того или иного сотрудника без использования группировки мы пошлем вот такой запрос:

А если нужно просуммировать другого сотрудника, то мы просто меняем условие. Согласитесь, если таких сотрудников много, зачем суммировать каждого, да и это как-то не наглядно, поэтому нам на помощь приходит оператор group by. Пишем запрос:

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

Примечание! Сразу отмечу то, что, сколько полей мы пишем в запросе (т.е. поля группировки), помимо агрегатных функций, столько же полей мы пишем в конструкции group by. В нашем примере мы выводим одно поле, поэтому в group by мы указали только одно поле (name), если бы мы выводили несколько полей, то их все пришлось бы указывать в конструкции group by (в последующих примерах Вы это увидите).

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

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

Теперь у нас все отображается, т.е. сколько денег поступило сотруднику, сколько раз, а также из какого источника.

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

Вот теперь все достаточно наглядно и не так уж сложно, даже для начинающих.

Также давайте затронем условия по итоговым результатам агрегатных функций (having). Другими словами, мы добавляем условие не по отбору самих строк, а уже на итоговое значение функций, в нашем случае это sum или count. Например, нам нужно вывести все то же самое, но только тех, у которых «всего денежных средств» больше 200. Для этого добавим условие having:

Теперь у нас вывелись все значения sum(summa), которые больше 200, все просто.

Заметка! Для профессионального изучения языка T-SQL рекомендую пройти наши онлайн-курсы по T-SQL.

Надеюсь, после сегодняшнего урока Вам стало понятно, как и зачем использовать конструкцию group by. Удачи! А SQL мы продолжим изучать в следующих статьях.

MS Access — Grouping Data

In this chapter, we will be covering how to calculate on a group of records in Access. We have created a field that calculates row by row or record by record to create a line total or subtotal field, but what if we wanted to calculate down by a group of records rather than by individual ones. We can do this by creating what’s known as an Aggregate Query.

Aggregate Query

An aggregate query also known as a totals or summary query is a sum, mass or group particulars. It can be a total or gross amount or a group or subset of records. Aggregate queries can perform a number of operations. Here’s a simple table listing some of the ways to total on a group of records.

Adds the field values

Average of the field values

Lowest (minimum) field value

Highest (maximum) field value

Count of the values (records)

Standard deviation of the field values including date/time fields

Variance of the field values including date/time

Let us open your database and go to the Query Design and include the following tables −

Close this dialog box and the following two fields will be displayed as shown in the query grid in the following screenshot.

This is a simple query and we are displaying only two fields — book title and quantity and when we run it we’re seeing every single individual order in our database.

Let us now run this query and you will see the following results.

This is why book titles are repeating. Separate orders have been placed for each of these books here and they were ordered in different quantities. Let us assume we want to see a summary of only that book title that has been listed only once. And then the sum of the quantity listed beside it.

Let us now go to the Design View and in the Design tab, you will see a Sigma symbol. This is your totals button.

Click the sigma symbol which will open another row underneath your field in the table row and from here, you can specify how you are grouping this query.

We will now group by book title and also sum our quantity field.

If we click on group by area and further click on the drop-down menu, all the options will be listed down. In this case, we will choose the option Sum and then run your query.

You can now see each individual book and also all the individual orders displayed beside the book name.

Concatenation in Access

We learned the process of normalization, storing information in separate fields. Sometimes you want to see or view data together like combining the first name and last name fields as a single field. You can display that information together by creating a calculated field that concatenates one or more strings. You can also add other characters like a comma or period that you may want.

To concatenate in Access, there are two different operators you can use the ampersand (&) and the plus (+) sign.

The ampersand will combine two strings into one whereas the plus sign will combine two strings and propagate NULL values, for example, if one value is NULL the entire expression evaluates to null.

Example

Let us take a simple example to understand the process of creating a new query using query design. This is going to be a very simple query that is pulling information from our customers’ table.

Let us now add tblCustomers table close that show table dialog box. We will try some concatenation with some simple named fields.

Let us now add the first name and last name fields and run the query.

As you can see, the first name and the last name are separated into two different fields.

We need to display this information together.

Go back to the Design View and in the third field create a new field called full name. Now, add the full name and type the expression that concatenates those two fields together.

Let us run the query and you will see a new calculated field.

It can concatenate the information from these two fields together, but it doesn’t exactly appear the way we want it to. It runs all of that text together because it’s performing the way we have asked it to. We will now have to ask Access to add space in between the information from these two fields.

Let us now go back to the Design View and add another section to this expression.

Add a Space inside quotes and another ampersand. This makes Access take the information from that first name field; add it with a space and then add the information from the last name field at the very end. Now, run the query again and you will see the following results.

SQL-запросы. Группировка данных Group By и Having

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

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

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

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

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

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

forum_group — имя группы

raiting — итоговый рейтинг пользователя

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

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

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

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

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

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

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

Обратите внимание, что после того, как вы использовали конструкцию 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-запрос

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