Agrupamento parcial com WITH ROLLUP no MySQL

Acho que desde que descobri o recurso de GROUP BY … WITH ROLLUP no MySQL, todas as consultas que faço pra relatórios incluem o modificador.

Uma query de exemplo seria pra consultar a quantidade de pessoas por estado, retornando também o total parcial por região.

Para que o MySQL já calcule este subtotal, basta incluir o modificador WITH ROLLUP logo após o GROUP BY:

SELECT uf.regiao, uf.nome, COUNT(p.id)
FROM uf
LEFT JOIN pessoa p ON uf.id = p.id_uf
GROUP BY uf.regiao, uf.nome WITH ROLLUP

+--------------+---------------------+-------------+
| regiao       | nome                | COUNT(p.id) |
+--------------+---------------------+-------------+
| Centro-Oeste | Distrito Federal    |          99 |
| Centro-Oeste | Goiás               |         114 |
| Centro-Oeste | Mato Grosso         |         102 |
| Centro-Oeste | Mato Grosso do Sul  |          91 |
| Centro-Oeste | NULL                |         406 |
| Nordeste     | Alagoas             |         107 |
| Nordeste     | Bahia               |          72 |
| Nordeste     | Ceará               |         100 |
| Nordeste     | Maranhão            |          89 |
| Nordeste     | Paraíba             |         128 |
| Nordeste     | Pernambuco          |         105 |
| Nordeste     | Piauí               |          92 |
| Nordeste     | Rio Grande do Norte |          83 |
| Nordeste     | Sergipe             |         115 |
| Nordeste     | NULL                |         891 |
| Norte        | Acre                |          87 |
| Norte        | Amapá               |          97 |
| Norte        | Amazonas            |         103 |
| Norte        | Pará                |         116 |
| Norte        | Rondônia            |         111 |
| Norte        | Roraima             |          93 |
| Norte        | Tocantins           |         101 |
| Norte        | NULL                |         708 |
| Sul          | Paraná              |         112 |
| Sul          | Rio Grande do Sul   |         110 |
| Sul          | Santa Catarina      |         115 |
| Sul          | NULL                |         337 |
| NULL         | NULL                |        2342 |
+--------------+---------------------+-------------+

 
Com o modificador WITH ROLLUP, para cada região existe um valor NULL na coluna do estado, representando o subtotal daquela região.

Esta regra vale para todos os níveis de agrupamento. As demais colunas agrupadas ficam com valor NULL quando a função de agregação, neste caso o COUNT, está representando um resultado parcial.

No final é retornado ainda o total geral, como se não houvesse agrupamento algum, deixando todas as colunas agrupadas com valor NULL.

Agora basta formatar o resultado pelo código e deixar as colunas de subtotal com algum estilo diferente e descrição apropriada.

Vale lembrar que o WITH ROLLUP retorna os rows ordenados pelas colunas agrupadas, não sendo permitido misturar uma cláusula ORDER BY na mesma query, até porque o MySQL se baseia na troca do valor da coluna para saber quando deve mostrar um total parcial (da mesma forma que faríamos manualmente). Caso seja realmente necessário, é possível utilizar o WITH ROLLUP em uma query derivada e a partir dela acrescentar o ORDER BY.

Veja mais detalhes sobre modificadores de agrupamento no capítulo GROUP BY Modifiers do manual.

* se alguém reparar que estão faltando os estados da região sudeste, é só porque eu esqueci de cadastrar no banco de testes mesmo.