30 TOTAL BY DEPT 9400

20 TOTAL BY DEPT 10875

GRAND TOTAL FOR TABLE 29025

В этом решении для Oracle при подготовке к конкатенации результаты, возвращаемые функциями GROUPING, неявно преобразуются в символьный тип данных. Пользователям DB2 и SQL Server придется явно приводить (используя функцию CAST) результаты функций GROUPING к типу CHAR(1), как показано в решении. Кроме того, в SQL Server для объединения результатов двух вызовов GROUPING в одну строку используется оператор конкатенации +, а не .

Пользователям Oracle и DB2 доступно дополнительное исключительно полезное расширение GROUP BY под названием GROUPING SETS. С помощью GROUPING SETS можно, например, имитировать вывод, создаваемый CUBE, как это сделано ниже (пользователям DB2 и SQL Server потребуется применить к значениям, возвращаемым функцией GROUPING, явные операторы CAST, как в решении с расширением

CUBE):

select deptno, job,

case grouping(deptno)grouping(job)

when 00 then TOTAL BY DEPT AND JOB

when 10 then TOTAL BY JOB

when 01 then TOTAL BY DEPT

when 11 then GRAND TOTAL FOR TABLE end category, sum(sal) sal from emp

group by grouping sets ((deptno),(job),(deptno,job),())

Можно также убрать какую-то из подсумм, например по DEPTNO, просто опуская (DEPTNO) в списке GROUPING SETS:

/* нет подсумм по DEPTNO */

select deptno, job,

case grouping(deptno)grouping(job)

when 00 then TOTAL BY DEPT AND JOB

GROUPING SETS замечателен тем, что позволяет задавать группы. Оператор GROUPING SETS в предыдущем запросе обусловливает создание групп по DEPTNO, по JOB, по сочетанию DEPTNO и JOB, и, наконец, пустые круглые скобки соответствуют запросу на получение общей суммы. GROUPING SETS обеспечивает колоссальную гибкость для создания отчетов с разными уровнями агрегации. Например, чтобы в предыдущем примере исключить из результирующего множества общую сумму (GRAND TOTAL), надо просто убрать из списка оператора GROUPING SETS пустые круглые скобки:

/* нет общей суммы */

select deptno, job,

case grouping(deptno)grouping(job)

when 00 then TOTAL BY DEPT AND JOB

when 10 then TOTAL BY JOB

when 01 then TOTAL BY DEPT

when 11 then GRAND TOTAL FOR TABLE

end category, sum(sal) sal from emp

group by grouping sets ((deptno),(job),(deptno,job))

when 10 then TOTAL BY JOB when 01 then TOTAL BY DEPT when 11 then GRAND TOTAL FOR TABLE end category,

sum(sal) sal from emp

group by grouping sets ((job),(deptno,job),()) order by 3

DEPTNO JOB

CATEGORY

Как видите, с GROUPING SETS очень просто манипулировать суммами и подсуммами, представляя данные в разных ракурсах.

PostgreSQL и MySQL

Первый шаг - использовать агрегатную функцию SUM и группировать значения по DEPTNO и JOB:

select deptno, job,

TOTAL BY DEPT AND JOB as category, sum(sal) as sal from emp

group by deptno, job

DEPTNO JOB

CATEGORY