Group by sql ms: GROUP BY (Transact-SQL) — SQL Server
Содержание
GROUPING (Transact-SQL) — SQL Server
-
- Чтение занимает 2 мин
В этой статье
Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics
Указывает, является ли указанное выражение столбца в списке GROUP BY статистическим или нет. В результирующем наборе функция GROUPING возвращает 1 (статистическое выражение) или ноль (нестатистическое выражение). Функция GROUPING может использоваться только в списке SELECT <select>, предложениях HAVING и ORDER BY, если указано предложение GROUP BY.
Синтаксические обозначения в Transact-SQL
Синтаксис
GROUPING ( <column_expression> )
Аргументы
<column_expression>
Столбец или выражение, которое содержит столбец в предложении GROUP BY.
Типы возвращаемых данных
tinyint
Remarks
GROUPING используется, чтобы различать значения NULL, возвращаемые операторами ROLLUP, CUBE или GROUPING SETS, и стандартные значения NULL. Возвращение NULL в качестве результата операции ROLLUP, CUBE или GROUPING SETS является особым случаем использования NULL. Значение служит заполнителем столбца в результирующем наборе и означает «все».
Примеры
В следующем примере производится группирование SalesQuota и статистическая обработка сумм SaleYTD в базе данных AdventureWorks2012. Функция GROUPING применяется к столбцу SalesQuota.
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
FROM Sales.SalesPerson
GROUP BY SalesQuota WITH ROLLUP;
GO
В результирующем наборе показаны два значения NULL в SalesQuota. Первый NULL представляет группу значений NULL из этого столбца в таблице.
Второй NULL находится в строке итогов, добавленной операцией ROLLUP. Строка итогов содержит суммы TotalSalesYTD для всех групп SalesQuota и обозначается с помощью 1 в столбце Grouping.
Результирующий набор:
SalesQuota TotalSalesYTD Grouping
------------ ----------------- --------
NULL 1533087.5999 0
250000.00 33461260.59 0
300000.00 9299677.9445 0
NULL 44294026.1344 1
(4 row(s) affected)
См. также:
GROUPING_ID (Transact-SQL)
GROUP BY (Transact-SQL)
ROLLUP, CUBE и GROUPING SETS операторы Transact-SQL для подведения итогов и промежуточных итогов | Info-Comp.ru
В статье мы рассмотрим возможность Transact-SQL формировать отчеты, как со строкой общего итога, так и со строками промежуточных итогов, для этих целей в MS SQL Server существуют такие операторы как ROLLUP, CUBE и GROUPING SETS, именно о них мы сегодня и поговорим.
Возможность построения отчетов на Transact-SQL очень полезная, мы с Вами уже об этом говорили, когда рассматривали оператор PIVOT, который может транспонировать набор данных, теперь давайте научимся писать запросы, в которых будут выделяться промежуточные итоги и итоги в целом. В Transact-SQL сделать это можно с помощью операторов ROLLUP, CUBE и GROUPING SETS.
Как Вы, наверное, догадываетесь, для того чтобы подсчитать итог или подытог, необходимо прибегнуть к агрегатным функциям и, соответственно, к группировке данных, поэтому операторы ROLLUP, CUBE и GROUPING SETS относятся к конструкции GROUP BY, т.е. являются расширением GROUP BY.
Эти операторы естественно отличаются друг от друга, поэтому в процессе рассмотрения мы будем их сравнивать, но для начала давайте определимся с тестовыми данными для примеров.
Исходные данные для примеров
В качестве SQL сервера у нас будет выступать Microsoft SQL Server Express 2014, а запросы будем писать в Management Studio Express.
Мы как всегда будем использовать выдуманные данные, и в этот раз я предлагаю вот такие, таблицу, которая будет содержать список сотрудников с указанием отдела, в котором они работают, а также сумму их заработка по годам.
Таблица
CREATE TABLE [dbo].[test_table](
[id] [INT] IDENTITY(1,1) NOT NULL,
[manager] [VARCHAR](50) NULL,
[otdel] [VARCHAR](50) NULL,
[god] [INT] NULL,
[summa] [MONEY] NULL
) ON [PRIMARY]
GO
А данные вот такие
Заметка! Если Вы не знаете, что делает вышеуказанная инструкция, рекомендую посмотреть мой видеокурс «T-SQL. Путь программиста от новичка к профессионалу. Уровень 1 – Новичок», который предназначен для начинающих. В нем подробно рассмотрены все базовые конструкции языка T-SQL.
ROLLUP
ROLLUP – оператор Transact-SQL, который формирует промежуточные итоги для каждого указанного элемента и общий итог.
Для того чтобы понять, как работает данный оператор, предлагаю сразу перейти к примерам, и допустим, что нам необходимо получить сумму расхода на оплату труда по отделам и по годам, и сначала давайте попробуем написать запрос с группировкой без использования оператора ROLLUP.
SELECT otdel, god, SUM(summa) AS itog FROM dbo.test_table GROUP BY otdel, god ORDER BY otdel, god
Как видите, группировка у нас получилась и в принципе мы видим что, например, в бухгалтерии в 2014 был такой расход, а 2015 такой, но иногда руководство хочет видеть и общую информацию, например, общий расход по каждому отделу. Для этих целей мы можем использовать оператор ROLLUP.
Текст запроса
SELECT otdel, god, SUM(summa) AS itog FROM dbo.test_table GROUP BY ROLLUP (otdel,god)
Строки со значением NULL и есть промежуточные итоги по отделам, а самая последняя строка общий итог.
Согласитесь, что это уже более наглядно.
Можно также использовать rollup и с группировкой по одному полю, например:
Группировка по отделам с общим итогом
Группировка по годам с общим итогом
CUBE
CUBE — оператор Transact-SQL, который формирует результаты для всех возможных перекрестных вычислений.
Давайте напишем практически такой же SQL запрос, только вместо rollup укажем cube и посмотрим на полученный результат.
Текст запроса
SELECT otdel, god, SUM(summa) AS itog FROM dbo.test_table GROUP BY CUBE (otdel,god)
В данном случае отличие от rollup заключается в том, что группировка и промежуточные итоги выполнены как для otdel, так и для god.
GROUPING SETS
GROUPING SETS – оператор Transact-SQL, который формирует результаты нескольких группировок в один набор данных, другими словами, он эквивалентен конструкции UNION ALL к указанным группам.
Пример GROUPING SETS
Текст запроса
SELECT otdel, god, SUM(summa) AS itog FROM dbo.test_table GROUP BY GROUPING SETS (otdel,god)
тот же результат, но с использованием UNION ALL
Текст запроса
SELECT null AS otdel, god, SUM(summa) AS itog FROM dbo.test_table GROUP BY god UNION ALL SELECT otdel, null AS god, SUM(summa) AS itog FROM dbo.test_table GROUP BY otdel
А сейчас предлагаю поговорить еще об одной полезной возможности, которая напрямую относится к перечисленным выше операторам, а именно о функции GROUPING.
GROUPING – функция Transact-SQL, которая возвращает истину, если указанное выражение является статистическим, и ложь, если выражение нестатистическое.
Данная функция создана для того, чтобы отличить статистические строки, которые добавил SQL сервер, от строк, которые и есть сами данные, так как когда используешь много группировок, запутаться в строках очень легко.
Пример GROUPING
Текст запроса
SELECT otdel,
ISNULL(CAST(god AS VARCHAR(30)),
CASE WHEN GROUPING(god)=1 ANS GROUPING(otdel)=0
THEN 'Промежуточный итог'
ELSE 'Общий итог' END) AS god,
SUM(summa) AS itog,
GROUPING(otdel) AS grouping_otdel,
GROUPING(god) AS grouping_god
FROM dbo.test_table
GROUP BY
ROLLUP (otdel,god)
На этом все, надеюсь, что возможность формировать итоги и промежуточные итоги в Microsoft SQL Server Вам будет полезна. Также рекомендую Вам почитать мою книгу «SQL код», в ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных. Удачи!
Нравится12Не нравится
MS-Access SQL различных GROUP BY
В настоящее время я пытаюсь SELECT DISTINCT FirstNames в GROUP , используя Microsoft Access 2010.
Упрощенные соответствующие столбцы моей таблицы выглядят следующим образом:
+----+-------------+-----------+
| ID | GroupNumber | FirstName |
+----+-------------+-----------+
| 1 | 1 | Peter |
| 2 | 1 | Bob |
| 3 | 1 | Peter |
| 4 | 2 | Rosemary |
| 5 | 2 | Jamie |
| 6 | 3 | Peter |
+----+-------------+-----------+
Моя фактическая таблица содержит два столбца, к которым я хочу применить этот процесс (отдельно), но я должен быть в состоянии просто повторить этот процесс для другого столбца. Номер группы столбцов-это упрощение, моя таблица фактически группирует все строки в десятидневном интервале вместе, но я уже решил эту проблему.
И я бы хотел, чтобы он вернул это:
+-------------+------------+
| GroupNumber | FirstNames |
+-------------+------------+
| 1 | Peter |
| 1 | Bob |
| 2 | Rosemary |
| 2 | Jamie |
| 3 | Peter |
+-------------+------------+
Это означает, что я хочу, чтобы все отдельные FirstNames были для каждой группы.
Обычный DISTINCT игнорирует границы группы и упоминает Питера только один раз. Все агрегатные функции сводят мой вывод только к одному значению или вообще не работают со строками. Access также не поддерживает столбцы SELECT ing, которые не являются агрегатами или находятся в операторе GROUP BY .
Все остальные ответы, которые я нашел, либо нуждаются в агрегате, либо не применимы к MS Access, либо решаются путем работы с данными способами, неприменимыми к моему случаю. (Стандартизированные языки-хорошая вещь, не так ли?)
Мой текущий (недопустимый) запрос выглядит следующим образом:
SELECT GroupNumber,
DISTINCT FirstNames -- This is illegal, distinct applies to all
-- columns and doesn't respect groups.
FROM Example AS b
-- Complicated stuff to make the groups
GROUP BY GroupNumber;
Этот запрос является одноразовым и используется для анализа электронной таблицы 58000 row excel, экспортированной из другой базы данных (не моя вина), поэтому оптимизация для времени выполнения не требуется.
Я хотел бы достичь этого чисто через SQL и без VBA, если это вообще возможно.
sql
ms-access
group-by
distinct
Поделиться
Источник
Alexander Hoischen
27 сентября 2016 в 17:11
2 ответа
- MS SQL-присоединиться к group by
Я очень новичок в SQL и учусь этому из книги SAMS Teach Yourself SQL in 24 hours. Моя забота — это все о соединениях и группировке выбранных выходных данных. Вот структура моих таблиц: CREATE TABLE ORDERS_TBL ( ORD_NUM VARCHAR(10) NOT NULL primary key, CUST_ID VARCHAR(10) NOT NULL, PROD_ID…
- SQL Group By запрос
У меня есть следующая таблица с одним столбцом ArbPlWPos : +————+ + IH-MKE + + IH-MKEEA + + IH-MKEEB + + IH-MKEPE + + IH-MKEPM + + IH-MVKE1 + + IH-MVKM1 + +————+ Я могу запустить оператор в MS Access, который группируется по первым 6 буквам: SELECT left(ArbPlWPos, 6),.
..
..
2
Это должно сработать:
SELECT DISTINCT GroupNumber, FirstNames
FROM Example AS b
Поделиться
Kashif Qureshi
27 сентября 2016 в 17:37
1
Решением этой проблемы было бы group by столбцы GroupNumber и FirstName одновременно. Запрос представлен ниже:
Select GroupNumber, FirstName
From input
Group By GroupNumber, FirstName
(Стандартизированные языки-хорошая вещь, не так ли?)
Поделиться
Quote
27 сентября 2016 в 22:31
Похожие вопросы:
ms access ошибка запроса
Я конвертирую свою базу данных из SQL Server в MS Access. Этот запрос отлично работает в SQL Server, но имеет синтаксическую ошибку в MS-Access.
Так что же не так с этим утверждением SQL?? select *…
MS Access для SQL вопрос запрос
У меня есть этот запрос, который должен использоваться в MS Access, но база данных — это база данных SQL. Когда я запускаю этот запрос в среде SQL, он работает отлично. Однако при запуске в MS…
Доступ SQL Group by с условием
Я использую MS Access для следующей задачи (из-за офисных ограничений). Я совсем новичок в SQL. У меня есть следующая таблица: Я хочу выбрать все магазины, сгруппированные по улице, zip и месту. Но…
MS SQL-присоединиться к group by
Я очень новичок в SQL и учусь этому из книги SAMS Teach Yourself SQL in 24 hours. Моя забота — это все о соединениях и группировке выбранных выходных данных. Вот структура моих таблиц: CREATE TABLE…
SQL Group By запрос
У меня есть следующая таблица с одним столбцом ArbPlWPos : +————+ + IH-MKE + + IH-MKEEA + + IH-MKEEB + + IH-MKEPE + + IH-MKEPM + + IH-MVKE1 + + IH-MVKM1 + +————+ Я могу запустить.
..
ms access GROUP BY ошибка
Я пытаюсь запустить этот SQL в MS доступе SELECT `orig`.`SONG TITLE`,`orig`.`PUBLISHER`,`orig`.`CFG DESCRIPTION` FROM `Sheet1` AS `orig` INNER JOIN `Sale type` AS `Sale` ON orig.`CFG…
Как выполнить чувствительный к регистру group by в MS Access
В базе данных MS Access найти несоответствие (т. е. разницу) записей между двумя таблицами(Employee и Employee_PROD) Я использую Союз все . Запрос выглядит следующим образом: SELECT…
MS Access Подсчет Различных Нескольких Столбцов
Я пытался написать запрос на основе SQL, используя Count distinct, но столкнулся с проблемами в том, как правильно структурировать формат для Microsoft Access. Мой текущий запрос находится в…
Group By и группы групп в MS Access
Я пытаюсь написать запрос в MS Access, используя group by, который приводит к подсчету двух групп, а затем к подсчету оставшихся групп вместе взятых.
Итак, если у меня есть цвет поля и 18 элементов,…
преобразование запроса access group by в запрос sql server
Я не могу преобразовать запрос MS Access в запрос SQL SERVER с изменением столбцов group by, потому что это повлияет на конечный результат. Целью данного запроса является расчет кредитора и должника…
SQL Предложение GROUP BY | Решения для баз данных для Microsoft Access
GROUP BY: Организационная статья SQL
GROUP BY — бесценный пункт SQL для организации
и выполнение операций с информацией о нескольких разных предметах
вы хотите агрегировать. Например, что, если вы хотите получить информацию
о посещаемости сотрудников в разные периоды времени, чтобы сделать
сравнения и графики? Или что, если вы хотите получить данные о продажах
для нескольких продавцов одной SQL-фразой?
SQL GROUP BY — правильный выбор, когда вы выбираете несколько
столбцы из одной или нескольких таблиц, а также выполнение математических
операции при их выборе.
Вы должны ГРУППИРОВАТЬ по всем остальным столбцам
кроме одного с математическим оператором. Вы не можете сделать больше
чем одна математическая операция. И вы не можете группировать по столбцам с
Свойства поля Memo, General или Blob.
Эта функция позволяет организовать возвращаемую информацию так, чтобы
ваша информация проста в использовании, она даже готова к распечатке в виде отчета.
Синтаксис группы SQL по:
ВЫБРАТЬ «имя_столбца1», СУММ («имя_столбца2») ОТ "table_name" ГРУППА ПО "имя_столбца1"
В этом синтаксисе «column_name1» представляет один или несколько столбцов.
возвращается по вашему запросу.Это может быть поле в таблице с именем FROM
оператор, псевдоним таблицы из списка SELECT или числовое выражение
с указанием позиции столбца, начиная с 1 слева.
Этот поиск вернет хорошо организованную таблицу, занимающую всю вашу
данные первого столбца и объедините их по имени, найдя суммы
все цифры второго столбца, которым соответствует первый столбец.
Например, если у вас есть таблица, содержащая информацию о нескольких
различные магазины и их показатели продаж, вы могли бы выяснить
продажи каждого с помощью следующей операции:
SELECT store_name, SUM (Продажи) ОТ Store_Information GROUP BY store_name
Результатом будет название магазина в первом столбце и сумма
общая сумма продаж для каждого во втором столбце.Вы можете дополнительно ограничить
сумма по периодам времени.
Помимо функции СУММ, вы можете использовать операции. Например, вы
можно использовать COUNT для выполнения простого подсчета инвентаря сотрудников,
или отфильтрованных элементов. Если вам нужно знать, сколько продуктов вы
в ценовом диапазоне $ 200-400, вы выполните следующее:
SELECT items, COUNT (*) как «200-400 $ Inventory Items»
ИЗ инвентаря
ГДЕ цена> = 200 И цена
Ваша возвращенная информация будет представлять собой однострочную таблицу, в которой рассказывается, как
много товаров в этом ценовом диапазоне.
Вы можете выполнять аналогичные функции с помощью MIN и MAX
Несколько столбцов группировки:
Когда вы используете несколько столбцов для группировки, вам нужно быть осторожным.
о заказе. Например, если вам нужна средняя зарплата каждого
по отделам, вы будете использовать те же два столбца, что и, если бы
вы хотели получить среднюю зарплату в каждом отделе по должностям, но
второй набор данных будет использоваться иначе, чем первый.
ВЫБОР отдел, работа
Средняя зарплата как "Средняя зарплата"
ИЗ списка сотрудников
ГРУППА ПО отделам, вакансиям
Этот запрос вернет среднюю зарплату по департаментам.
и рабочие места в каждом отделе.
Общие сведения о функциях GROUPING и GROUPING_ID в SQL Server — {coding} Взгляд
Операторы ROLLUP и CUBE используются для возврата результатов, агрегированных по столбцам в предложении GROUP BY.
Функции GROUPING и GROUPING_ID используются для определения того, агрегируются ли столбцы в списке GROUP BY (с использованием операторов ROLLUP или CUBE) или нет.
Есть два основных различия между функциями GROUPING и GROUPING_ID.
Это следующие:
- Функция GROUPING применима к одному столбцу, тогда как список столбцов для функции GROUPING_ID должен соответствовать списку столбцов в предложении GROUP BY.
- Функция GROUPING указывает, является ли столбец в списке GROUP BY агрегированным или нет.Он возвращает 1, если набор результатов агрегирован, и 0, если набор результатов не агрегирован.
С другой стороны, функция GROUPING_ID также возвращает целое число. Однако он выполняет преобразование двоичного кода в десятичное после объединения результатов всех функций ГРУППИРОВКИ.
В этой статье мы увидим функции GROUPING и GROUPING_ID в действии с помощью примеров.
Подготовка фиктивных данных
Как всегда, давайте создадим фиктивные данные, которые мы собираемся использовать для примера, с которым мы будем работать в этой статье.
Выполните следующий сценарий:
CREATE База данных компании;
Компания USE;
СОЗДАТЬ ТАБЛИЦУ сотрудника
(
id INT ПЕРВИЧНЫЙ КЛЮЧ,
name VARCHAR (50) NOT NULL,
пол VARCHAR (50) NOT NULL,
зарплата INT NOT NULL,
отдел VARCHAR (50) NOT NULL
)
ВСТАВИТЬ сотрудника
ЗНАЧЕНИЯ
(1, Дэвид, Мужчина, 5000, Продажи),
(2, «Джим», «Женский», 6000, «HR»),
(3, «Катя», «Женщина», 7500, «ИТ»),
(4, «Воля», «Мужской», 6500, «Маркетинг»),
(5, «Шейн», «Женский», 5500, «Финансы»),
(6, Сарай, Мужской, 8000, Продажи),
(7, Вик, Мужской, 7200, HR),
(8, «Винс», «Женщина», 6600, «ИТ»),
(9, «Джейн», «Женщина», 5400, «Маркетинг»),
(10, «Лаура», «Женский», 6300, «Финансы»),
(11, Mac, Мужской, 5700, Продажи),
(12, «Пат», «Мужской», 7000, «HR»),
(13, «Джули», «Женщина», 7100, «ИТ»),
(14, «Элис», «Женщина», 6800, «Маркетинг»),
(15, Уэйн, Мужчина, 5000, Финансы)
В приведенном выше сценарии мы создали базу данных с именем «Компания».Затем мы создали таблицу «Сотрудник» в базе данных компании.
Наконец, мы вставили несколько фиктивных записей в таблицу Employee.
ГРУППИРОВКА Функция
Как упоминалось выше, функция GROUPING возвращает 1, если набор результатов агрегирован, и 0, если набор результатов не агрегирован.
Взгляните на следующий сценарий, чтобы увидеть, как работает функция ГРУППИРОВКА.
ВЫБРАТЬ отдел АС Отдел, пол КАК Пол, sum (зарплата) как Salary_Sum, ГРУППИРОВКА (отдел) как GP_Department, ГРУППА (пол) как GP_Gender ОТ сотрудника ГРУППА ПО РОЛИКЕ (отдел, пол)
Приведенный выше сценарий подсчитывает сумму зарплат всех сотрудников мужского и женского пола, которые сгруппированы сначала по столбцу «Отдел», а затем по столбцу «Пол».Добавлены еще два столбца для отображения результата применения функции ГРУППИРОВКА к столбцам «Отдел» и «Пол».
Оператор ROLLUP используется для отображения суммы зарплат в виде общих и промежуточных итогов.
Результат выполнения сценария выше выглядит следующим образом.
Внимательно посмотрите на вывод. Сумма зарплат отображается по полу по полу отдела (строки 1, 2, 4, 5, 7, 9, 10 и 12). Затем он также агрегируется только по полу (строки 3, 6, 8, 11 и 13).Наконец, в строке 14 отображается общая сумма зарплат, агрегированная по департаментам и гендерным вопросам.
1 отображается в столбце функции ГРУППИРОВКА GP_Gender для строк, где результаты агрегированы по полу, т.е. строки 3, 6, 8, 11 и 13. Это связано с тем, что столбец GP_Gender содержит результат функции ГРУППИРОВКА, примененной к столбцу Пол.
Точно так же строка 14 содержит агрегированную сумму всех отделов и всех столбцов. Поэтому для столбцов GP_Department и GP_Gender возвращается 1.
Вы можете видеть, что NULL отображается в столбцах «Отдел» и «Пол» в выходных данных, где результаты суммируются. Например, в строке 3 в столбце «Пол» отображается NULL, поскольку результаты агрегированы по столбцу «Пол» и поэтому нет значения столбца для отображения. Мы не хотим, чтобы наши пользователи видели NULL, лучшим словом здесь могло бы быть «Все полы».
Для этого нам нужно изменить наш скрипт следующим образом:
ВЫБРАТЬ СЛУЧАЙ, КОГДА ГРУППА (отдел) = 1 ТОГДА 'Все отделы' ИНАЧЕ ЕСТЬ НУЛЬ (отдел, "Неизвестно") КОНЕЦ как Отдел СЛУЧАЙ, КОГДА ГРУППА (пол) = 1 ТОГДА «Все полы» ИНАЧЕ НЕ НУЖНА (пол, «Неизвестно») END как Пол, сумма (зарплата) как Salary_Sum ОТ сотрудника ГРУППА ПО РОЛИКЕ (отдел, пол)
В приведенном выше сценарии, если функция ГРУППИРОВКА, примененная к столбцу «Отдел», возвращает 1, а в столбце «Отдел» отображается «Все отделы».В противном случае, если столбец «Отдел» содержит значение NULL, будет отображаться «Неизвестно». Столбец «Пол» был изменен таким же образом.
Выполнение приведенного выше сценария возвращает следующие результаты:
Вы можете видеть, что NULL в столбцах «Отдел» и «Пол», где функция ГРУППИРОВКА возвращает 1, был заменен на «Все отделы» и «Все полы» соответственно.
GROUPING_ID Функция
Функция GROUPING_ID объединяет вывод функций GROUPING, применяемых ко всем столбцам, указанным в предложении GROUP BY.Затем он выполняет преобразование из двоичного в десятичное, прежде чем вернуть окончательный результат.
Давайте сначала объединим вывод, возвращаемый функцией GROUPING, примененной к столбцам «Отдел» и «Пол». Взгляните на следующий скрипт:
ЕГЭ компания ВЫБРАТЬ отдел АС Отдел, пол КАК Пол, sum (зарплата) как Salary_Sum, CAST (ГРУППИРОВКА (отдел) КАК VARCHAR (1)) + CAST (GROUPING (пол) AS VARCHAR (1)) как группировка ОТ сотрудника ГРУППА ПО РОЛИКЕ (отдел, пол)
В выводе вы увидите 0 и 1, возвращаемые функцией GROUPING, соединенные вместе.Результат выглядит так:
Функция GROUPING_ID просто возвращает десятичный эквивалент двоичного значения, сформированного в результате конкатенации значений, возвращаемых функциями GROUPING.
Выполните следующий сценарий, чтобы увидеть в действии функцию GROUPING ID:
ЕГЭ компания ВЫБРАТЬ отдел АС Отдел, пол КАК Пол, sum (зарплата) как Salary_Sum, CAST (ГРУППИРОВКА (отдел) КАК VARCHAR (1)) + CAST (GROUPING (пол) AS VARCHAR (1)) как группировка, GROUPING_ID (отдел, пол) как Grouping_Id ОТ сотрудника ГРУППА ПО РОЛИКЕ (отдел, пол)
Для строки 1 функция GROUPING ID вернет 0, поскольку десятичный эквивалент «00» равен нулю.
Для строк 3, 6, 8, 11 и 13 функция GROUPING_ID возвращает 1, поскольку десятичный эквивалент «01» равен 1.
Наконец, для строки 14 функция GROUPIND_ID возвращает 3, поскольку двоичный эквивалент «11» равен 3.
Результат выполнения сценария выше выглядит следующим образом:
См. Также:
Microsoft: Обзор Grouping_ID
Microsoft: Обзор группировки
YouTube: Grouping & Grouping_ID
Бен Ричардсон руководит организацией Acuity Training.Acuity — это компания, занимающаяся обучением в сфере ИТ, предлагающая аудиторные курсы в Лондоне и Гилфорде. Он является ведущим поставщиком обучения SQL в Великобритании и предлагает полный спектр обучения SQL от вводного обучения до курсов продвинутого администрирования. Последние сообщения Бена Ричардсона (посмотреть все)
Групповые функции SQL Server | Рам Кедем
Это руководство по SQL фокусируется на групповых функциях SQL в SQL Server и содержит объяснения, примеры и упражнения. Для упражнений этого урока используйте эту ссылку.
Определение
Групповые функции SQL Server работают с наборами строк, чтобы дать один результат для каждой группы, например:
Общие групповые функции SQL Server
| Функция | Описание | Синтаксис |
| СУММ | Возвращает общую сумму | СУММА (зарплата) - Результат: 20000 |
| МИН | Возвращает наименьшее значение | МИН (зарплата) - Результат: 1000 |
| МАКС | Возвращает максимальное значение | МАКС (зарплата) - Результат: 7000 |
| СРЕДНЯЯ | Возвращает среднее значение | AVG (зарплата) - Результат: 4000 |
| СЧЕТЧИК (*) | Возвращает количество записей в таблице | СЧИТАТЬ(*) - Результат: 5 |
| COUNT (столбец) | Возвращает количество значений (значения NULL не учитываются) указанного столбца | COUNT (имя) - Результат: 4 |
| COUNT (DISTINCT столбец) | Возвращает количество различных значений | COUNT (ОТЛИЧНОЕ имя) - Результат: 3 |
* Результаты основаны на вышеприведенной иллюстрации
Функции группы SQL Server и NULL
- В SQL Server все групповые функции игнорируют значения NULL.Например: средняя зарплата рассчитывается на основе строк в таблице, где хранится действительное значение (общая зарплата, деленная на количество сотрудников, получающих зарплату).
- В SQL Server Вы можете использовать функцию ISNULL, чтобы заставить групповые функции включать значения NULL, в следующем примере среднее значение вычисляется на основе всех строк в таблице, независимо от того, хранятся ли значения NULL в столбце зарплаты (общая зарплата деленное на общее количество строк в таблице):
ВЫБРАТЬ СРЕДНЕЕ (ISNULL (зарплата, 0)) ОТ сотрудников
Предложение SQL Server GROUP BY
ВЫБЕРИТЕ имя_столбца, функцию_группы (имя_столбца) FROM table_name ГДЕ условие ГРУППА ПО имя_столбца
До сих пор каждая описанная здесь групповая функция обрабатывала таблицу как одну большую группу данных.В большинстве случаев вам нужно разделить таблицу на более мелкие группы, вместо того, чтобы получать среднюю зарплату всех сотрудников в таблице Сотрудники, вы бы предпочли увидеть, например, среднюю зарплату, сгруппированную по каждому отделу (какова средняя зарплата HR отдел, ИТ-отдел и так далее).
Вы можете использовать предложение SQL Server GROUP BY для разделения строк в таблице на группы. Затем вы можете использовать групповые функции для получения сводной информации по каждой группе.
ВЫБЕРИТЕ Department_id, AVG (зарплата) ОТ сотрудников ГРУППА ПО идентификатору отдела
Несколько указаний
Все столбцы в предложении SQL Server SELECT, которые не являются групповыми функциями, должны быть в предложении GROUP BY
При указании групповых функций (AVG) в предложении SELECT, наряду с другими отдельными элементами (Department_id), вы должны включить предложение GROUP BY.В предложении GROUP BY вы должны указать эти отдельные элементы (в данном случае Department_id), иначе будет сгенерирована ошибка.
Столбцы SQL Server GROUP BY не обязательно должны быть в предложении SQL Server SELECT
Совершенно возможно группировать по разным столбцам, но не указывать эти столбцы в предложении SQL Server SELECT (однако результат не будет иметь смысла). В этом примере мы отображаем среднюю зарплату для каждого отдела без отображения номеров соответствующих отделов.
ВЫБЕРИТЕ СРЕДНЮЮ (зарплату) ОТ сотрудников ГРУППА ПО идентификатору отдела
Вы можете указать несколько столбцов после предложения SQL Server GROUP BY
Иногда вам нужно увидеть результат для групп внутри групп, например: в каждом отделе есть разные должности (административные должности, клерк, обслуживающий персонал и т. Д.). Хотя операции, выполненные до сих пор, были предназначены для отображения среднего значения по каждому отделу, указанный ниже запрос позволяет увидеть среднее значение по каждому отделу, разделенное по типу должности:
ВЫБЕРИТЕ Department_id, job_id, AVG (зарплата) ОТ сотрудников ГДЕ Department_id IN (50, 80, 90) ГРУППА ПО идентификатору отдела, идентификатору задания
Используя предложение SQL Server WHERE, вы можете исключить строки перед их разделением на группы.
Например, если вам нужно отобразить среднюю зарплату только для отделов 50, 80 и 90:
ВЫБЕРИТЕ Department_id, AVG (зарплата) ОТ сотрудников ГДЕ Department_id IN (50, 80, 90) ГРУППА ПО идентификатору отдела
Вы не можете использовать предложение SQL Server WHERE для ограничения групп
Как показано в последнем примере, с помощью предложения SQL Server WHERE можно ограничить строки перед их разделением на группы.Однако невозможно указать групповые функции в предложении SQL Server WHERE, так как это приведет к ошибке.
Отделения, в которых средняя заработная плата выше 5000:
ВЫБЕРИТЕ Department_id, AVG (зарплата) ОТ сотрудников ГДЕ СРЕДН (зарплата)> 5000 ГРУППА ПО идентификатору отдела (ошибка)
Оговорка HAVING
Предложение SQL Server HAVING позволяет фильтровать агрегированные результаты, полученные с помощью предложения SQL Server GROUP BY.Точно так же, как вы использовали предложение WHERE SQL Server для ограничения строк, вы используете предложение SQL Server HAVING для ограничения групп.
ВЫБЕРИТЕ имя_столбца, функцию_группы (имя_столбца) FROM table_name ГДЕ условие ГРУППА ПО имя_столбца ИМЕЮЩИЕ условие
Отделения, в которых средняя заработная плата выше 5000:
ВЫБЕРИТЕ Department_id, AVG (зарплата) ОТ сотрудников ГРУППА ПО идентификатору отдела ИМЕЕТ СРЕДНЮЮ (зарплату)> 5000
В одном запросе можно использовать как предложения SQL Server HAVING, так и WHERE.Из отделов 80, 50 и 90 отделения, в которых средняя заработная плата выше 5000:
ВЫБЕРИТЕ Department_id, AVG (зарплата) ОТ сотрудников ГДЕ Department_id IN (50, 80, 90) ГРУППА ПО идентификатору отдела ИМЕЕТ СРЕДНЮЮ (зарплату)> 5000
Вы можете фильтровать на основе другой групповой функции, отличной от той, которая отображается в операторе SELECT SQL Server:
ВЫБЕРИТЕ Department_id, AVG (зарплата) ОТ сотрудников ГДЕ Department_id IN (50, 80, 90) ГРУППА ПО идентификатору отдела ИМЕЮЩИЙ МАКС (зарплата)> 5000
Microsoft Access GROUP BY Запрос
В Microsoft Access GROUP BY — это предложение, которое можно использовать для объединения записей с идентичными значениями в определенном поле в одну запись.Если вы включите агрегатную функцию SQL в оператор SELECT, такую как AVG, COUNT или SUM, Access создаст итоговое значение для каждой записи.
Инструкции в этой статье относятся к Access для Microsoft 365, Access 2019, 2016, 2013 и 2010.
Михайломилованович / Getty Images
Использование GROUP BY
Вы можете найти и использовать функцию GROUP BY, используя SQL-запрос в представлении SQL. Это один из самых простых и прямых способов доступа к вашим данным и управления ими.
Запустите Access и откройте свою базу данных.
Выберите вкладку Create .
В группе Запросы выберите Дизайн запросов .
В списке Добавить таблицы выберите таблицу, с которой вы хотите работать.
Выберите View в группе результатов и выберите SQL View .
Основное тело переключится в окно терминала запросов.Здесь вы можете ввести любой запрос.
Чтобы получить базовую группировку из SQL, вы должны ввести что-то вроде этого:
ВЫБЕРИТЕ * ИЗ имя таблицы ГДЕ столбец / категория КАК «запись»;
Подставьте фактическое имя таблицы, заголовок категории или столбца и фактическое значение записи, которое вы ищете.
Разбор запроса
Рассмотрим, например, таблицу данных заказа, состоящую из следующих атрибутов:
- OrderID : числовое значение, однозначно идентифицирующее каждый заказ.Это поле является первичным ключом для базы данных.
- Продавец : текстовое значение, содержащее имя продавца, продавшего товары. Это поле является внешним ключом для другой таблицы, содержащей информацию о персонале.
- CustomerID : числовое значение, соответствующее номеру счета клиента. Это поле также является внешним ключом, ссылаясь на таблицу, содержащую информацию об учетной записи клиента.
- Доход : числовое значение, соответствующее долларовой сумме продажи.
Когда приходит время проводить оценку эффективности для продавцов, таблица «Заказы» содержит ценную информацию, которая может быть использована для этого обзора. При оценке Джима вы можете, например, написать простой запрос, который извлекает все записи Джима о продажах:
ВЫБЕРИТЕ * ИЗ заказов, ГДЕ продавец НРАВИТСЯ «Джим»;
Это приведет к извлечению всех записей из базы данных, соответствующих продажам Джима:
OrderID Продавец CustomerID Доход
12482 Jim 182 40000
12488 Jim 219 25000
12519 Jim 137 85000
12602 Jim 182 10000
12741 Jim 155
Вы можете просмотреть эту информацию и выполнить некоторые ручные вычисления для получения статистики производительности, но это будет утомительная задача, которую вам придется повторять для каждого продавца в компании.Вместо этого вы можете заменить эту работу одним запросом GROUP BY, который вычисляет статистику каждого продавца в компании. Вы пишете запрос и указываете, что база данных должна сгруппировать результаты на основе поля «Продавец». Затем вы можете использовать любую из агрегатных функций SQL для выполнения вычислений по результатам.
Вот пример. Если вы выполнили следующий оператор SQL:
ВЫБЕРИТЕ продавца, СУММ (доход) КАК "Итого", МИН (доход) КАК "Наименьшее", МАКС (доход) КАК "Наибольшее", СРЕДНЕЕ (доход) КАК "Среднее", COUNT (доход) КАК "Число" ИЗ ГРУППЫ заказов. ПО продавцу;
Вы получите следующие результаты:
Всего наименьшее наибольшее среднее число
Джим 250000 1000050000 5
Мэри 342000 24000 102000 57000 6
Боб 118000 4000 36000 39333 3
Как видите, эта мощная функция позволяет создавать краткие отчеты из запроса SQL, предоставляя ценную бизнес-аналитику менеджеру, проводящему анализ производительности.Предложение GROUP BY часто используется в базах данных для этой цели и является ценным инструментом в пакете уловок администратора баз данных.
Спасибо, что сообщили нам!
Расскажите, почему!
Другой
Недостаточно подробностей
Сложно понять
SQL Server GROUP BY operator
SQLS * Plus — лучший инструмент для создания отчетов и автоматизации из командной строки SQL Server! SQLS * Plus на несколько порядков лучше, чем инструменты командной строки SQL Server sqlcmd и osql.
Главная »Блог SQL Server» SQL Server GROUP BY оператор
26 июня 2020
Оператор SQL Server GROUP BY может использоваться в операторе SELECT для сбора данных по нескольким записям и группировки результатов по одному или нескольким столбцы.
Синтаксис оператора GROUP BY в SQL
SELECT expr1, expr2,… expr_n,
aggregate_function (aggregate_expr)
FROM tab
[WHERE conds]
…
[ЗАКАЗАТЬ ПО expr [ASC | DESC]];
где:
- expr1,2,… _n — Выражения, которые не инкапсулированы в агрегатной функции и должны быть включены в GROUP BY в конце запроса SQL.
- aggr_function — это агрегатная функция, такая как SUM, COUNT, MIN, MAX или AVG.
- aggr_expression — это столбец или выражение, для которого будет использоваться агрегатная функция.
- вкладка — Таблицы, из которых вы хотите сделать записи. Предложение FROM должно содержать хотя бы одну таблицу.
- WHERE conds — Необязательно. Это условия, которые должны быть выполнены для выбора записей.
- ЗАКАЗАТЬ В — Необязательно.Выражение, используемое для сортировки записей в наборе результатов. Если указано более одного выражения, значения должны быть разделены запятыми.
- ASC — Необязательно. ASC сортирует набор результатов в порядке возрастания. Это поведение по умолчанию, если не указан модификатор.
- DESC — Необязательно. DESC сортирует набор результатов в порядке убывания.
Использование GROUP BY с функцией SUM
Давайте посмотрим, как использовать GROUP BY с функцией SUM в SQL.
В этом примере у нас есть таблица со следующими данными:
| emp_number | f_name | l_name | sal | dept |
|---|---|---|---|---|
| 10017 | Just4 | 500 | ||
| 1002 | Селена | Гомес | 57500 | 500 |
| 1003 | Мила | Кунис | 71000 9017 9017 9017 9017 9017 9017 9017 9017 9017 9017 42000 | 501 |
Введите следующий оператор SQL:
SELECT dept,
SUM (зарплата) AS total_sal
FROM сотрудников
GROUP BY dept;
Будут выбраны две записи.Вот результаты, которые вы получите:
| dept | total_sal | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 500 | 119500 | |||||||||||||
| 501 | 113000 |
| prod_id | prod_name | cat_id | ||
|---|---|---|---|---|
| 1 | Груша Банан | 50 | ||
| 3 | Оранжевый | 50 | ||
| 4 | Яблоко | 50 | ||
| 5 | Хлеб | 75 | ||
| 7 | Kleenex | NULL |
Введите следующий оператор SQL:
SELECT cat_id,
COUNT (*) AS total_prod_prod
NULL
ГРУППА ПО cat_id
ORDER BY cat_id;
Будут выбраны три записи.Вот результаты, которые вы должны получить:
| cat_id | total_prod | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 25 | 1 | |||||||||||||
| 50 | 4 | 75174 В этом примере мы использовали функцию COUNT, чтобы вычислить количество total_prod для каждого cat_id, и мы указали псевдоним «total_prod» как результат функции COUNT. Мы исключили все значения cat_id, которые имеют значение NULL, и отфильтровали их в предложении WHERE.Поскольку cat_id не инкапсулирован в функции COUNT, он должен быть указан в предложении GROUP BY.Использование GROUP BY с функцией MINДавайте теперь посмотрим, как использовать предложение GROUP BY с функцией MIN в SQL. В этом примере мы снова будем использовать таблицу со следующими данными:
Введите следующий оператор SQL: Будут выбраны две записи.Вот результаты, которые вы получите:
Введите следующий оператор SQL: Будут выбраны две записи.Вот результаты, которые вы должны получить:
Пример №1 В результате для каждой из групп (23 года, 24 года и 25 лет) будет рассчитана общая заработная плата внутри этой группы: Запрос SQL выберет следующие строки:
|