Дополнительно к стандартным операторам GROUP BY и HAVING SQL Server поддерживает еще четыре специальных расширения для группировки данных: ROLLUP, CUBE, GROUPING SETS и OVER.
Оператор 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 похож на ROLLUP за тем исключением, что CUBE добавляет суммирующие строки для каждой комбинации групп.
SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units FROM Products GROUP BY Manufacturer, ProductCount WITH CUBE
Оператор 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 позволяет суммировать данные, при этому возвращая те строки, которые использовались для получения суммированных данных. Например, найдем количество моделей и общее количество товаров этих моделей по производителю:
SELECT ProductName, Manufacturer, ProductCount, COUNT(*) OVER (PARTITION BY Manufacturer) AS Models, SUM(ProductCount) OVER (PARTITION BY Manufacturer) AS Units FROM Products
Выражение OVER ставится после агрегатной функции, затем в скобках идет выражение PARTITION BY и столбец, по которому выполняется группировка.
То есть в данном случае мы выбираем название модели, производителя, количество единиц модели и добавляем к этому количество моделей для данного производителя и общее количество единиц всех моделей производителя: