Subtotais em Oracle SQL - parte I

A clausula GROUP BY é bastante conhecida para obter dados agregados. Por exemplo, a consulta abaixo obtem a soma dos salários por departamento e ano de contratação:

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

Department_Name	Hire_Year	SUM(Salary)
IT	2005	4800
IT	2006	13800
IT	2007	10200
Sales	2004	53500
Sales	2005	100300
Sales	2006	59100
Sales	2007	42200
Sales	2008	49400

Mas se estiver preparando um report, é provável que o requisito inclua linhas de subtotais. Neste ponto entra uma opção bem menos conhecida, o GROUP BY ROLLUP. Veja a mesma consulta utilizando esta opção. As linhas destacadas em verde são novas e representam subtotais e o total geral.

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

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
		333300

O ROLLUP não precisa incluir todas as colunas. Neste caso, a consulta retorna subtotais apenas para as colunas não citadas no ROLLUP. Ou, em outros termos, os totais deconsideram os valores individuais das colunas incluídas no ROLLUP. Veja o exemplo abaixo:

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

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

Para finalizar, todos os exemplos utilizaram a função SUM, mas é possível utilizar qualquer outra função, como MIN, MAX, AVG e COUNT.

Na parte II falarei sobre o CUBE, outra variação de GROUP BY que pode ser útil para obter subtotais e totais.

Parte 1

Parte 2

Parte 3