SUBTOTAIS EM ORACLE SQL - PARTE II

Outra clausula que pode ser utilizada é o GROUP BY CUBE. A principal diferença é que ela retorna subtotais individuais para cada coluna listada, enquanto o ROLLUP retorna subtotais agregando apenas a última coluna. Ou seja, em geral, CUBE retornará mais linahs que ROLLUP. Por exemplo:

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', 'Sales')
GROUP BY CUBE(department_name, TO_CHAR(hire_date, 'yyyy'))
ORDER BY 1,2;

Resposta - linhas verdes mostram subtotais por Department_Name; azuis, por Hire_Year; e amarela, o grande total.:

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

Digamos agora que outras colunas relativas a departamento são necessárias, como endereço e cidade. Obviamente, cada departamento tem apenas um endereço e cidade e talvez não seja necessário calcular subtotais separados para cada uma destas colunas. Para resolver esta situação, existe o GROUP BY GROUPING SETS. 

A consulta abaixo define dois grupos para calcular subtotais, cada grupo definido por parenteses. O primeiro inclui três colunas: Department_Name, Street_Address e City. O segundo apenas uma coluna: Hire_Year.

SELECT department_name, 

       street_address, 

       city,

       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)
JOIN HR.locations loc USING (location_id)

WHERE department_name IN ( 'IT', 'Sales')

GROUP BY GROUPING SETS((department_name, street_address, city) , 

                       (TO_CHAR(hire_date, 'yyyy'))

                       )

ORDER BY 1,4;

A consulta retorna apenas linhas de subtotais para os grupos indicados.

Department_Name	Street_Address	City	Hire_Year	SUM(Salary)
IT	2014 Jabberwocky Rd	Southlake		28800
Sales	Magdalen Centre, The Oxford Science Park	Oxford		304500
			2004	53500
			2005	105100
			2006	72900
			2007	52400
			2008	49400

E como obter as linhas de detalhe, que foram somadas? Simples, basta combinar com uma consulta baseada apenas em GROUP BY através de UNION ALL:

SELECT department_name, 

       street_address, 

       city,

       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 department_name, street_address, city, TO_CHAR(hire_date, 'yyyy')

ORDER BY 1,4;

UNION ALL
SELECT department_name, 

       street_address, 

       city,

       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 GROUPING SETS((department_name, street_address, city) , 

                       (TO_CHAR(hire_date, 'yyyy'))

                       )

ORDER BY 1,4;


Na terceira parte mostrarei a função GROUPING, muito útil para formatar resultados incluindo linhas de detalhe e linhas de subtotal.

Parte 1

Parte 2

Parte 3