MySQL - Tabelas virtuais - Fazendo Subselects e agrupamentos com GROUP BY

Hoje vamos ver uma query bem simples mas extremamente útil. Ela envolve o princípio de subselects que nada mais é do que a possibilidade que o SQL nos dá de criar uma espécie de tabela virtual através de uma query e, posteriormente, utilizar esta mesta para fazer uma nova query. É como aplicar um "filtro" duas vezes para tornar mais "pura" uma mistura.

Outro modo de fazer isto seria criando uma VIEW, porém, muitas vezes, é completamente desnecessário como no caso de um select simples. VIEWS geralmente guardamos para selects muito complexos.

Bem, vamos começar com nossa tabela base de testes.

id  nome  idade  estado
1  Pedro  50   SP
2  Antonio  30   RJ
3  Amélia  20   SP
4  Marta  15   SP
5  Maria  80   SP

Vamos supor que desejamos agrupar as informações por estado. Isto é bem simples através das linhas abaixo:

SELECT estado,count(*) AS quantidade FROM `teste` GROUP BY estado

A saída é:

estado quantidade
RJ 1
SP 4

Na query acima 'count(*) AS quantidade' cria um 'apelido' ou alias para a quantidade de ocorrências de 'estado'. Na verdade isto é apenas uma firula para tornar mais visual o retorno.

Bem, mas e se quisermos saber a quantidade total de estados?

Então utilizamos a query abaixo:

SELECT COUNT(*) AS estados FROM (SELECT estado,count(*) AS quantidade FROM `teste` GROUP BY estado) AS t1

O que ela faz é utilizar o resultado da primeira query como se fosse uma tabela (colocar o alias 'AS t1' faz com que ela se comporte como tal) e, sendo assim, aplica a segunda query na primeira.

Temos como resultado:

estados
2

Poderíamos fazer este mesmo select apenas utilizando:

SELECT COUNT(DISTINCT estado) AS estados FROM `teste`

Porém no caso em que você precisasse fazer algo mais complexo como, por exemplo, agrupar também pela primeira letra de cada nome:

SELECT estado,SUBSTRING(nome,1,1) AS primeira_letra,COUNT(*) AS quantidade FROM `teste` GROUP BY estado,SUBSTRING(nome,1,1)

estado primeira_letra quantidade
RJ A 1
SP A 1
SP M 2
SP P 1

E depois descobrir a quantidade de resultados

SELECT COUNT(*) AS quantidade FROM (SELECT estado,SUBSTRING(nome,1,1) AS primeira_letra,COUNT(*) AS quantidade FROM `teste` GROUP BY estado,SUBSTRING(nome,1,1)) AS t2

quantidade
4

Como vc faria utilizando DISTINCT ?

Estes exemplos são muito simples mas são a base para que você possa entender a mecânica dos subselects e utilizar em jobs mais complexos.

Um abraço a todos.