Расширения SQL Server для группировки

Дополнительно к стандартным операторам GROUP BY и HAVING SQL Server поддерживает еще четыре специальных расширения для группировки данных: ROLLUP, CUBE, GROUPING SETS и OVER.

ROLLUP

Оператор ROLLUP добавляет суммирующую строку в результирующий набор:

SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
GROUP BY Manufacturer WITH ROLLUP

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

Альтернативный синтаксис запроса, который можно использовать, начиная с версии MS SQL Server 2008:

SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
GROUP BY ROLLUP(Manufacturer)

При группировке по нескольким критериям ROLLUP будет создавать суммирующую строку для каждой из подгрупп:

SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
GROUP BY Manufacturer, ProductCount WITH ROLLUP

При сортировке с помощью ORDER BY следует учитывать, что она применяется уже после добавления суммирующей строки.

CUBE

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

SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
GROUP BY Manufacturer, ProductCount WITH CUBE

GROUPING SETS

Оператор GROUPING SETS аналогично ROLLUP и CUBE добавляет суммирующую строку для групп. Но при этом он не включает сами группам:

SELECT Manufacturer, COUNT(*) AS Models, ProductCount
FROM Products
GROUP BY GROUPING SETS(Manufacturer, ProductCount)

При этом его можно комбинировать с ROLLUP или CUBE. Например, кроме суммирующих строк по каждой из групп добавим суммирующую строку для всех групп:

SELECT Manufacturer, COUNT(*) AS Models, 
		ProductCount, SUM(ProductCount) AS Units
FROM Products
GROUP BY GROUPING SETS(ROLLUP(Manufacturer), ProductCount)

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

SELECT Manufacturer, COUNT(*) AS Models, 
		ProductCount, SUM(ProductCount) AS Units
FROM Products
GROUP BY GROUPING SETS((Manufacturer, ProductCount), ProductCount)

OVER

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

SELECT ProductName, Manufacturer, ProductCount,
		COUNT(*) OVER (PARTITION BY Manufacturer) AS Models,
		SUM(ProductCount) OVER (PARTITION BY Manufacturer) AS Units
FROM Products

Выражение OVER ставится после агрегатной функции, затем в скобках идет выражение PARTITION BY и столбец, по которому выполняется группировка.

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