Microsoft SQL Server
ГРУППА ПО

Простая группировка

Таблица заказов

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

SELECT customerId
FROM orders
GROUP BY customerId;

Возвращаемое значение:

Совокупные функции, такие как count() применяются к каждой группе, а не к полной таблице:

SELECT customerId, 
       COUNT(productId) as numberOfProducts,
       sum(price) as totalPrice
FROM orders
GROUP BY customerId;

Возвращаемое значение:

ГРУППА несколькими столбцами

Можно захотеть GROUP BY более чем одним столбцом

declare @temp table(age int, name varchar(15))

insert into @temp
select 18, 'matt' union all
select 21, 'matt' union all
select 21, 'matt' union all
select 18, 'luke' union all
select 18, 'luke' union all
select 21, 'luke' union all
select 18, 'luke' union all
select 21, 'luke'

SELECT Age, Name, count(1) count
FROM @temp 
GROUP BY Age, Name

будут группироваться по возрасту и имени и будут производить:

Группировать с помощью нескольких таблиц, несколько столбцов

Группа часто используется с заявлением о соединении. Предположим, у нас есть две таблицы. Первая - таблица студентов:

Вторая таблица - это таблица темы, которую может принять каждый студент:

И потому, что один студент может посещать множество предметов, и один предмет может посещать многие ученики (поэтому отношение N: N), нам нужно иметь третью «ограничительную» таблицу. Назовем таблицу Students_subjects:

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

Select Students.FullName, COUNT(Subject Id) as SubjectNumber FROM Students_Subjects
LEFT JOIN Students
ON Students_Subjects.Student_id = Students.Id
GROUP BY Students.FullName

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

Для еще более сложного примера использования GROUP BY, скажем, студент может присвоить одно и то же имя своему имени более одного раза (как показано в таблице Students_Subjects). В этом случае мы могли бы подсчитать количество раз, когда каждый предмет был назначен ученику по GROUPing более чем на один столбец:

SELECT Students.FullName, Subjects.Subject,
COUNT(Students_subjects.Subject_id) AS NumberOfOrders
FROM ((Students_Subjects
INNER JOIN Students
ON Students_Subjcets.Student_id=Students.Id)
INNER JOIN Subjects
ON Students_Subjects.Subject_id=Subjects.Subject_id)
GROUP BY Fullname,Subject

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

HAVING

Поскольку WHERE оценивается до GROUP BY , вы не можете использовать WHERE для оценки результатов группировки (как правило, агрегированную функцию, такую ​​как COUNT(*) ). Чтобы удовлетворить эту потребность, можно использовать предложение HAVING .

Например, используя следующие данные:

DECLARE @orders TABLE(OrderID INT, Name NVARCHAR(100))

INSERT INTO @orders VALUES
( 1, 'Matt' ),
( 2, 'John' ),
( 3, 'Matt' ),
( 4, 'Luke' ),
( 5, 'John' ),
( 6, 'Luke' ),
( 7, 'John' ),
( 8, 'John' ),
( 9, 'Luke' ),
( 10, 'John' ),
( 11, 'Luke' )

Если мы хотим получить количество заказов, которые разместили каждый человек, мы будем использовать

SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name

и получить

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

SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name
HAVING COUNT(*) > 2

даст

Обратите внимание, что, подобно GROUP BY , столбцы, помещенные в HAVING должны точно соответствовать их аналогам в SELECT . Если бы в приведенном выше примере мы сказали, что

SELECT Name, COUNT(DISTINCT OrderID)

наше предложение HAVING должно было бы сказать

HAVING COUNT(DISTINCT OrderID) > 2

GROUP BY с ROLLUP и CUBE

Оператор ROLLUP полезен при создании отчетов, содержащих промежуточные итоги и итоговые значения.

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

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

  Вещь	цвет	Количество
  Таблица	синий	124
  Таблица	красный	223
  кресло	синий	101
  кресло	красный	210

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item                 Color                QtySum                     
-------------------- -------------------- -------------------------- 
Chair                Blue                 101.00                     
Chair                Red                  210.00                     
Chair                ALL                  311.00                     
Table                Blue                 124.00                     
Table                Red                  223.00                     
Table                ALL                  347.00                     
ALL                  ALL                  658.00 

(Затронуто 7 строк)

Если ключевое слово ROLLUP в запросе будет изменено на CUBE, набор результатов CUBE будет таким же, за исключением того, что в конце будут возвращены эти две дополнительные строки:

ALL                  Blue                 225.00                     
ALL                  Red                  433.00 

https://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx