Banco de Dados Introducao

8-Agrupando Dados

Nessa seção vamos rever alguns procedimentos para agruparmos resultados das consultas.

Já vimos algumas funções que agupam (agregam) valores (como COUNT e SUM, por exemplo) e a cláusula GROUP BY.

Vamos agora formalizar esses conceitos.

Vamos usar o nosso banco de dados CDTeca para fazer alguns exemplos básicos.

Funções de Agregação (ou Funções Agregadas)

Veja uma tabela com algumas funções que são usadas para calcular/retornar resultados únicos a partir de uma massa de dados:

Tabela Algumas Funções Agregadas

Vide mais sobre agregate functions (funções agregadas)no manual de referência do Mysql.

(Talvez fosse mais adequado chamá-las de "funções que agregam"...ou Funções Agregadoras para uma tradução mais próxima do termo em inglês.).

Dentre as funções de agregação fornecidas pelo Mysql (vide lista das agregate functions) vamos destacar algumas mais usadas.

Contagem - Função count()

A função COUNT recebe um parâmetro (nome de um campo ou *) e retorna um número

Alguns exemplos

Exemplos com COUNT()

                            
mysql> select count(*) from CDs;
+----------+
| count(*) |
+----------+
|        6 |
+----------+

mysql> select count(id)
    -> from CDs
    -> where mod(id,2)=0
    -> ;
+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)


mysql> select count(Pais)
    -> from CDs
    -> where Pais="Brasil";
+-------------+
| count(Pais) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)


mysql> select count(*) from CDs where TipoMusica ='MPB';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)


mysql> select count(*) from CDs where TipoMusica LIKE 'MPB%';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from CDs where TipoMusica = 'MPB%';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Soma - Função sum()

O uso da função SUM() é bem simples. Basta chamarmos a função passando para ela a coluna que desejamos obter a soma.

Exemplos com SUM()

                            
mysql> select SUM(NumMusicas) from CDs;
+-----------------+
| SUM(NumMusicas) |
+-----------------+
|              77 |
+-----------------+
1 row in set (0.00 sec)


mysql> select sum(NumMusicas) from CDs where id<3;
+-----------------+
| sum(NumMusicas) |
+-----------------+
|              22 |
+-----------------+
1 row in set (0.00 sec)

Média (Average) - Função avg()

O uso da função AVG() (average, média) é também muito simples. Usamos a média dos valores de uma coluna fazendo AVG(NOME-da-Coluna).

Veja os exemplos:

Exemplos com AVG()

                            

mysql> select avg(NumMusicas)
    -> from CDs;
+-----------------+
| avg(NumMusicas) |
+-----------------+
|         12.8333 |
+-----------------+
1 row in set (0.00 sec)

mysql> select avg(NumMusicas) from CDs
    -> where Autor = 'Pink Floyd';
+-----------------+
| avg(NumMusicas) |
+-----------------+
|         18.0000 |
+-----------------+
1 row in set (0.00 sec)

Máximo e Mínimo - Funções max() e min()

Assim como as funções anteriores, o uso de MIN() e MAX() é simples (e óbvio). Elas retornam, respectiamente os valores mínimos e máximos de uma coluna.

Exemplos com MAX() e MIN()

                            

mysql> select MAX(NumMusicas) as 'Máximo de Músicas', MIN(NumMusicas) as 'Mínimo de Músicas'
    -> from CDs;
+---------------------+---------------------+
| Máximo de Músicas   | Mínimo de Músicas   |
+---------------------+---------------------+
|                  25 |                   9 |
+---------------------+---------------------+
1 row in set (0.01 sec)


mysql> select MAX(NumMusicas) as 'Máximo de Músicas', MIN(NumMusicas) as 'Mínimo de Músicas' from CDs
    -> where Pais = 'Brasil';
+---------------------+---------------------+
| Máximo de Músicas   | Mínimo de Músicas   |
+---------------------+---------------------+
|                  12 |                   9 |
+---------------------+---------------------+
1 row in set (0.00 sec)

A Cláusula GROUP BY

As funções apresentadas acima realizam operações sobre conjuntos de dados (por isso são agregate functions). Elas transformam um conjunto de dados (uma coluna, por exemplo) e um único valor.

Podemos por exemplo obter a soma total do número de músicas.

Mas como fazer para somar o número de músicas para um determinado autor?

Para isso precisamos agrupar o resultado da soma para cada Autor.

É aí que entra a cláusula GROUP BY

Sua sintaxe é:


SELECT col1,col2,col3,....colN,
funcao1,...funcaoN 
from NOME_TABELA
where CONDIÇÃO
GROUP BY col1,col2,col3,...coN;

Vejamos alguns exemplos:

Exemplos com GROUP BY

                            
mysql> select Pais,count(Pais)
    -> from CDs
    -> group by Pais;
+------------+-------------+
| Pais       | count(Pais) |
+------------+-------------+
| Brasil     |           3 |
| Inglaterra |           3 |
+------------+-------------+
2 rows in set (0.00 sec)



mysql> select Autor,sum(NumMusicas)
    -> from CDs
    -> group by Autor;
+-------------------------+-----------------+
| Autor                   | sum(NumMusicas) |
+-------------------------+-----------------+
| Elomar, Xangai e Outros |              22 |
| Pink Floyd              |              36 |
| Fleetwood Mac           |              10 |
| Chico Buarque           |               9 |
+-------------------------+-----------------+
4 rows in set (0.00 sec)

mysql> select Autor, max(NumMusicas) from CDs Group By Autor;
+-------------------------+-----------------+
| Autor                   | max(NumMusicas) |
+-------------------------+-----------------+
| Elomar, Xangai e Outros |              12 |
| Pink Floyd              |              25 |
| Fleetwood Mac           |              10 |
| Chico Buarque           |               9 |
+-------------------------+-----------------+
4 rows in set (0.00 sec)

A Cláusula HAVING

(Vide mais sobre HAVING aqui ou aqui )

A cláusula WHERE não nos permite realizar restrições com base nos resultados das funções de agreagação.

No caso de necessitarmos fazer alguma restrição nos resultados das funções de agregação devemos usar a cláusula HAVING.

Assim como a cláusula WHERE, a cláusula HAVING deverá ser seguida de uma condição lógica.

Vejamos alguns exemplos

Vamos usar a cláusula HAVING para fazer algums consultas específicas no Banco CDS.

Vamos fazer as seguintes buscas:

Quais os autores (em todos CDs) que possuem mais de 30 músicas?
E mais de 20?

Exemplos com HAVING

                            
mysql> select Autor,sum(NumMusicas) as Total  from CDs group by Autor;
+-------------------------+-------+
| Autor                   | Total |
+-------------------------+-------+
| Elomar, Xangai e Outros |    22 |
| Pink Floyd              |    36 |
| Fleetwood Mac           |    10 |
| Chico Buarque           |     9 |
+-------------------------+-------+
4 rows in set (0.00 sec)

mysql> select Autor,sum(NumMusicas) as Total  from CDs group by Autor
    -> having Total > 30;
+------------+-------+
| Autor      | Total |
+------------+-------+
| Pink Floyd |    36 |
+------------+-------+
1 row in set (0.00 sec)

mysql> select Autor,sum(NumMusicas) as Total 
 -> from CDs group by Autor 
 -> having Total > 20;
+-------------------------+-------+
| Autor                   | Total |
+-------------------------+-------+
| Elomar, Xangai e Outros |    22 |
| Pink Floyd              |    36 |
+-------------------------+-------+
2 rows in set (0.01 sec)


mysql> select Autor,sum(NumMusicas) as Total  from CDs group by Autor where Total > 20;
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'where Total > 20' at line 1

Quais os Autores que tiveram 2 ou mais CDs lançados?

Exemplos com HAVING

                            

mysql> select Autor,Count(*)
    -> from CDs
    -> Group By Autor
    -> having count(*) > 1;
+-------------------------+----------+
| Autor                   | Count(*) |
+-------------------------+----------+
| Elomar, Xangai e Outros |        2 |
| Pink Floyd              |        2 |
+-------------------------+----------+
2 rows in set (0.00 sec)

Repare que podemos agrupar por Autor e por Ano, por exemplo: (na verdade, nesse caso não agrupou por não haver resultado semelhante para agrupar na condição dada, ou seja, o count(*) contou apenas 1)

Exemplos com HAVING

                            
mysql> select Autor, Count(*),Ano from CDs Group By Autor, Ano Having Ano > 1978;
+-------------------------+----------+------+
| Autor                   | Count(*) | Ano  |
+-------------------------+----------+------+
| Elomar, Xangai e Outros |        1 | 1984 |
| Elomar, Xangai e Outros |        1 | 1988 |
| Pink Floyd              |        1 | 1983 |
| Pink Floyd              |        1 | 1979 |
+-------------------------+----------+------+
4 rows in set (0.00 sec)

Ou agrupar por Autor, Ano e Pais (na verdade, nesse caso não agrupou por não haver resultado semelhante para agrupar na condição dada, ou seja, o count(*) contou apenas 1)

Exemplos com HAVING

                            
mysql> select Autor, Count(*),Ano, Pais from CDs Group By Autor, Ano, Pais 
Having Ano > 1978 and Pais= 'Brasil';
+-------------------------+----------+------+--------+
| Autor                   | Count(*) | Ano  | Pais   |
+-------------------------+----------+------+--------+
| Elomar, Xangai e Outros |        1 | 1984 | Brasil |
| Elomar, Xangai e Outros |        1 | 1988 | Brasil |
+-------------------------+----------+------+--------+
2 rows in set (0.00 sec)

voltar ao início da página

Página Anterior Próxima Página