Получение итоговых данных с помощью оператора ROLLUP

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

SELECT point, SUM(inc) Qty
FROM Income GROUP BY point;

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

Для решения подобной задачи в операторе SELECT имеется спецификация ROLLUP. С её помощью достичь требуемого результата не составляет труда:

SELECT 
    CASE 
        WHEN point IS NULL 
        THEN 'ALL' 
        ELSE CAST(point AS VARCHAR) 
    END point,
    SUM(inc) Qty
FROM Income 
GROUP BY point WITH ROLLUP;

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

Последний запрос можно переписать в иной (стандартной) синтаксической форме:

select 
    case 
        when point is null 
        then 'ALL' 
        else cast(point as varchar) 
    end point,
    sum(inc) Qty
from Income group by ROLLUP(point);

Вместо ROLLUP в нашем запросе можно также использовать оператор CUBE:

SELECT 
    CASE 
        WHEN point IS NULL 
        THEN 'ALL' 
        ELSE CAST(point AS VARCHAR) 
    END point,
    SUM(inc) Qty
FROM Income
GROUP BY point WITH CUBE;

Подробно о различиях между этими двумя операторами вы можете почитать в статье Бена Ричардсона.

Если СУБД не поддерживает конструкцию ROLLUP, можно использовать либо UNION, либо внешнее соединение (FULL JOIN), что позволяет объединить два запроса в один.

Ниже приводятся эти решения.

UNION

select cast(point as varchar) point, sum(inc) Qty
from Income group by point
union ALL
select 'ALL', sum(inc)
from Income;

FULL JOIN

select coalesce(X.point,Y.point) point, coalesce(X.Qty,Y.Qty) Qty 
from (select cast(point as varchar) point, sum(inc) Qty
      from Income group by point
     ) X
   full join
     (select 'ALL' point, sum(inc) Qty
      from Income
     ) Y on 1 = 2;

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

Рекомендуемые упражнения: 120