Subtotais em Oracle SQL - parte III

A combinação de GROUP BY com ROLLUP, CUBE ou GROUPING_SETS resulta em dois tipos de linhas: agregadas, aquelas com valores em todas as colunas do GROUP BY; e super-agregadas, as linhas de subtotais ou totais.

A função GROUPING permite facilmente dferenciar as duas, pois retorna o valor 0 se a linha representar um valor agregado e o valor 1 caso seja um super-agregado. Veja na consulta abaixo os valroes da primeira e segunda coluna. A última linha, por ser o grande total, tem valor 1 para ambas. 

SELECT grouping(department_name) grp_dept,

      grouping(TO_CHAR(hire_date, 'yyyy')) grp_hd,

       department_name,
       TO_CHAR(hire_date, 'yyyy') hire_year,
       SUM(salary)
FROM hr.employees emp

JOIN HR.departments dept USING (department_id)

JOIN HR.jobs j USING (job_id)
WHERE department_name IN ( 'IT', 'Sales')
GROUP BY CUBE(department_name, TO_CHAR(hire_date, 'yyyy'))
ORDER BY 1,2;

Grp_Dept	Grp_HD	Department_Name	Hire_Year	SUM(Salary)
0	0	IT	2005	4800
0	0	Sales	2007	42200
0	0	Sales	2006	59100
0	0	Sales	2005	100300
0	0	Sales	2004	53500
0	0	IT	2007	10200
0	0	IT	2006	13800
0	0	Sales	2008	49400
0	1	Sales		304500
0	1	IT		28800
1	0		2008	49400
1	0		2007	52400
1	0		2006	72900
1	0		2005	105100
1	0		2004	53500
1	1			333300

Obviamente 0 ou 1 podem ser úteis para algum tipo de processamento automático, mas caso queira mostrar um resultado final para usuários, é mais conveniente substituir por mensagens adequadas. Por exemplo, a primeira coluna é resultado da função GROUPING numa combinação com CASE para definir um valor que o usuário final receberá:

SELECT case when grouping(department_name) = 1 AND grouping(TO_CHAR(hire_date, 'yyyy')) = 1 then 'Grande total'
            when grouping(department_name) = 1 then 'Subtotal por ano'
            when grouping(TO_CHAR(hire_date, 'yyyy')) = 1 then  'Subtotal por departamento'
            else null
       end column_type,

       department_name,
       TO_CHAR(hire_date, 'yyyy') hire_year,
       SUM(salary)
FROM hr.employees emp

JOIN HR.departments dept USING (department_id)

JOIN HR.jobs j USING (job_id)
WHERE department_name IN ( 'IT', 'Sales')
GROUP BY CUBE(department_name, TO_CHAR(hire_date, 'yyyy'))
ORDER BY 1,2;

Column_Type	Department_Name	Hire_Year	SUM(Salary)
	IT	2005	4800
	IT	2006	13800
	IT	2007	10200
Subtotal by department	IT		28800
	Sales	2004	53500
	Sales	2005	100300
	Sales	2006	59100
	Sales	2007	42200
	Sales	2008	49400
Subtotal by department	Sales		304500
Subtotal by year		2004	53500
Subtotal by year		2005	105100
Subtotal by year		2006	72900
Subtotal by year		2007	52400
Subtotal by year		2008	49400
Grand total			333300

Por fim, uma nota referente a clausula HAVING: ela filtra linhas agregadas que atendam (ou não) o predicado, mas todas linhas são consideradas para calcular os subtotais e totais. Isto pode resultar em uma certa inconsistência nos dados.  Por exemplo, a consulta abaixo retorna duas linhas para os anos 2006 e 2007 em que mais de um funcionário foi contratado. Mas o valor na linha e subtotal (e total) é resultado da soma de três anos!

SELECT department_name,
       TO_CHAR(hire_date, 'yyyy') hire_year,
       SUM(salary)
FROM hr.employees emp
JOIN HR.departments dept USING (department_id)
JOIN HR.jobs j USING (job_id)
WHERE department_name IN ( 'IT')
GROUP BY department_name, ROLLUP(TO_CHAR(hire_date, 'yyyy'))

HAVING COUNT(*) > 1
ORDER BY 1,2;

Department_Name	Hire_Year	SUM(Salary)
IT	2006	13800
IT	2007	10200
IT		28800
		28800

Uma possível solução é utilizar subconsultas:

SELECT department_name,
       hire_year,
       SUM(sum_salary)

FROM(

     SELECT department_name,
            TO_CHAR(hire_date, 'yyyy') hire_year,
            SUM(salary) sum_salary
     FROM hr.employees emp
     JOIN HR.departments dept USING (department_id)
     JOIN HR.jobs j USING (job_id)
     WHERE department_name IN ( 'IT')
     GROUP BY department_name, TO_CHAR(hire_date, 'yyyy')
     HAVING COUNT(*) > 1)

GROUP BY ROLLUP(department_name, hire_year)

ORDER BY 1,2;

Department_Name	Hire_Year	SUM(Salary)
IT	2006	13800
IT	2007	10200
IT		24000
		24000

Espero que tenha sido claro nas explicações. Por favor, qualquer dúvida, sugestão, etc...  utilizem os comentários.

Parte 1

Parte 2

Parte 3