Funções de Agregação em Consultas SQL

Prévia do material em texto

Capítulo 6 
Somando, contando e agrupando 
Funções de agregação são extremamente úteis na confecção de consultas que efetuam cálculos 
de consolidação da base de dados. Contar registros, obter o somatório de certos atributos, 
calcular médias, máximos e mínimos são exemplos típicos das operações de agregação. Este 
capítulo trata da construção de consultas SQL que empregam funções de agregação. 
6.1 AGREGANDO VALORES 
Como escrever uma consulta que obtenha a média das notas dos alunos? Com o que vimos até 
aqui, é impraticável1 construir uma consulta dessas. É preciso somar os valores de todas as 
notas e dividir esta soma pelo número de notas. Mesmo na álgebra relacional, vista no capítulo 
3, não há operações com essas características. 
Esta dificuldade levou os projetistas das linguagens de consulta a introduzir a idéia de funções 
de agregação, que são funções aplicadas sobre colunas. A consulta SQL abaixo, que calcula a 
média das notas dos alunos, é um exemplo 
select avg (nota) as media_das_notas 
from inscricao 
que produz como resultado 
 
media_das_notas 
6,74285711560931
 
A função avg (da palavra average, que em inglês significa média) é um exemplo de função de 
agregação. Uma função de agregação opera sempre sobre uma coluna ou uma expressão que 
combine valores de uma ou mais colunas. No caso acima, a função avg operou sobre a coluna 
nota, produzindo sua média. 
Podemos conferir este resultado incluindo a soma e o número de notas existentes na base de 
dados. A consulta 
select sum (nota) as soma_das_notas, 
 count (nota) as numero_de_notas, 
 avg (nota) as media_das_notas 
from inscricao 
produz 
soma_das_notas numero_de_notas media_das_notas 
47,2 7 6,74285711560931 
 
1 Impraticável, mas não impossível. Veja o exercício 6.10 no final deste capítulo. 
2 Capítulo 6: Somando, contando e agrupando 
 
 
Aqui usamos três funções de agregação na mesma consulta: avg, count e sum. A primeira 
calcula a média, a segunda conta as notas e a terceira acumula a soma total. As três operam 
sobre a coluna nota, independentemente. 
Uma terceira versão da consulta, indicando também as notas mínima e máxima, com as funções 
min e max, poderia ser escrita como 
select sum(nota) as soma_das_notas, 
 count(nota) as numero_de_notas, 
 avg(nota) as media_das_notas, 
 min (nota) as nota_minima, 
 max (nota) as nota_maxima 
from inscricao 
produzindo 
 
soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 
47,2 7 6,74285711560931 3 10 
 
Note que somente sete notas, das nove existentes na base exemplo, foram computadas. Isso 
ocorre porque valores nulos não são considerados. 
6.2 A CLÁUSULA GROUP BY 
Vamos supor agora que fosse necessário produzir estatísticas equivalentes, porém mostrando-as 
por disciplina. Ou seja, é preciso produzir a média das notas por disciplina, a nota mínima por 
disciplina, e assim por diante. Para simplificar, vamos calcular inicialmente somente a média 
das notas. A consulta 
 
select coddisciplina, 
 avg (nota) as nota_media_disciplina 
from inscricao 
group by coddisciplina 
produz 
 
coddisciplina nota_media_disciplina
112 NULL 
114 7,00 
117 4,20 
210 9,50 
316 6,50 
317 6,75 
 
A novidade nesta consulta é a cláusula group by, que estabelece que o resultado deve ser 
agrupado pelos diferentes valores do atributo coddisciplina e que as funções de agregação sejam 
aplicadas a cada um desses grupos separadamente. 
 A figura a seguir ilustra o agrupamento de registros que originou o resultado da consulta 
anterior. O conteúdo da tabela INSCRIÇÃO, cujas colunas foram utilizadas no cômputo das 
funções de agregação é mostrado por ordem do atributo coddisciplina. Para cada valor deste 
atributo, que é pivô na cláusula group by, a função avg calcula a média das notas 
correspondentes. O primeiro grupo inclui duas notas com valores nulos para a disciplina de 
Error! Style not defined. 3 
 
código 112, o que origina a primeira linha do resultado. A média de valores nulos é sempre 
nula. O último grupo, para a disciplina 317, inclui duas notas que têm por média 6,75. 
 
A mesma idéia, agora com as demais funções de agregação. 
select coddisciplina, 
 sum (nota) as soma_das_notas, 
 count (nota) as numero_de_notas, 
 avg (nota) as media_das_notas, 
 min (nota) as nota_minima, 
 max (nota) as nota_maxima 
from inscricao 
group by coddisciplina 
O resultado apresenta cada uma das estatísticas anteriores computadas separadamente para cada 
diferente código de disciplina. Note que no caso de notas com valores nulos todos os resultados 
calculados são nulos, exceto o de contagem (função count) que é zero. 
 
coddisciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima
112 NULL 0 NULL NULL NULL 
114 7 1 7 7 7 
117 4,2 1 4,2 4,2 4,2 
210 9,5 1 9,5 9,5 9,5 
316 13 2 6,5 3 10 
317 13,5 2 6,75 5,5 8 
 
De uma forma geral, valores nulos são ignorados pelas funções de agregação. Assim, 
numa coluna onde haja valores nulos e não nulos somente os não nulos são considerados. 
A CLÁUSULA GROUP BY NO MYSQL 
O MySQL admite que uma coluna referenciada na cláusula group by seja identificada pela sua 
posiçao relativa na lista-alvo ou seu alias. Assim, seria possível uma consulta que normalmente 
seria escrita como 
select coddisciplina, 
 avg (nota) as nota_media_disciplina 
from inscricao 
group by coddisciplina 
poderia ser escrita como 
select coddisciplina as dis, 
 avg (nota) as nota_media_disciplina 
from inscricao 
group by dis 
Matricula CodDisciplina Nota 
1001 112 NULL 
1007 112 NULL 
1007 114 7,00 
1005 117 4,2 
1002 210 9,5 
1005 316 3,0 
1010 316 10,00 
1001 317 8,0 
1010 317 5,5 
coddisciplina nota_media_disciplina
112 NULL 
114 7 
117 4,2 
210 9,5 
316 6,5 
317 6,75 
4 Capítulo 6: Somando, contando e agrupando 
 
ou como 
select coddisciplina, 
 avg (nota) as nota_media_disciplina 
from inscricao 
group by 1 
 
Nos três casos, os resultados são idênticos. 
AGRUPANDO VALORES NULOS 
Valores nulos que eventualmente ocorram nas expressões que determinam os grupamentos são 
mantidos em grupos separados. Suponha a consulta que produz o número de alunos para os 
quais cada diferente nota foi atribuída. 
select nota, count(*) as alunos 
from inscricao 
group by nota 
order by 1 
No resultado 
nota alunos 
NULL 2 
3 1 
4,2 1 
5,5 1 
7 1 
8 1 
9,5 1 
10 1 
 
pode-se notar que as notas nulas foram grupadas num único grupo. Ou seja, para efeito da 
cláusula group by, valores nulos são considerados iguais entre si. Um fato curioso neste 
exemplo é que, no Oracle, o registro que contém o valor nulo é o último do resultado, enquanto 
que para o Access, MySQL e SQL Server é o primeiro, como mostrado acima. 
6.3 A CLÁUSULA HAVING 
A cláusula having possibilita a filtragem de linhas do resultado após a aplicação das funções de 
agregação. Vamos supor que, no exemplo acima, seja necessário mostrar apenas as disciplinas 
que têm mais de uma nota. A consulta seria reescrita como 
select coddisciplina, 
 sum (nota) as soma_das_notas, 
 count (nota) as numero_de_notas, 
 avg (nota) as media_das_notas, 
 min (nota) as nota_minima, 
 max (nota) as nota_maxima 
from inscricao 
group by coddisciplina 
having count (nota) > 1 
para produzir como resultado 
 
coddisciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 
Error! Style not defined. 5 
 
316 13 2 6,5 3 10 
317 13,5 2 6,75 5,5 8 
 
Tanto a cláusula where como a cláusula having filtram linhas do resultado. A diferença é 
que, na cláusula where, a expressão lógica deve ser verdadeira antes que uma 
determinada combinação de registros possa formar uma linha do resultado; na cláusulahaving, 
as linhas são filtradas depois das funções de agregação terem sido aplicadas sobre as colunas. 
Este conceito ficará mais claro mais adiante neste capítulo, na seção que aborda a construção do 
resultado de consultas com funções de agregação. 
O critério de filtragem de uma cláusula having é uma expressão lógica genérica, porém sujeita à 
seguinte restrição: somente um atributo ou expressão referenciado na cláusula group by pode 
aparecer fora da aplicação de uma função agregada. Possibilidades adicionais são apresentadas 
no capítulo 9. 
Tomando-se como exemplo a consulta 
select coddisciplina, 
 sum (nota) as soma_das_notas, 
 count (nota) as numero_de_notas, 
 avg (nota) as media_das_notas, 
 min (nota) as nota_minima, 
 max (nota) as nota_maxima 
from inscricao 
group by coddisciplina 
nota-se que os termos lógicos aplicáveis a uma possível cláusula having somente poderiam fazer 
referência ao atributo coddisciplina (porque aparece na cláusula group by). Os demais atributos 
envolvidos presentes na fonte de registros (INSCRIÇÃO) poderiam ser referenciados apenas como 
parte de uma função de agregação. Assim, a cláusula 
having coddisciplina > 200 and max (nota) < 9 
seria uma cláusula válida, porque nota aparece numa função de agregação, mas 
having coddisciplina > 200 and nota < 9 
não seria aceita, porque o atributo nota não é referenciado na cláusula group by. 
6.4 COMBINANDO TABELAS E APLICANDO FUNÇÕES DE AGREGAÇÃO 
O resultado da consulta anterior ficaria melhor se as disciplinas fossem identificadas pelos seus 
nomes ao invés dos códigos. Para fazer isso, entretanto, é preciso combinar as tabelas INSCRIÇÃO 
e DISCIPLINA. A consulta a seguir ilustra o uso de consultas com junções e funções de agregação. 
select i.coddisciplina, 
 d.disciplina, 
 sum (i.nota) as soma_das_notas, 
 count (i.nota) as numero_de_notas, 
 avg (i.nota) as media_das_notas, 
 min (i.nota) as nota_minima, 
 max (i.nota) as nota_maxima 
from inscricao i, disciplina d 
where i.coddisciplina = d.coddisciplina 
group by i.coddisciplina, d.disciplina 
order by d.disciplina 
O resultado produzido é mostrado abaixo. 
 
coddisciplina disciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima
210 Compiladores 9,5 1 9,5 9,5 9,5 
112 Dir. Constitucional NULL 0 NULL NULL NULL 
114 Direito Civil 7 1 7 7 7 
6 Capítulo 6: Somando, contando e agrupando 
 
117 Estatística 4,2 1 4,2 4,2 4,2 
317 Português 13,5 2 6,75 5,5 8 
316 Sociologia 13 2 6,5 3 10 
 
Note que há dois atributos, coddisciplina e disciplina, referenciados na cláusula group by. Isto 
porque os cálculos das funções de agregação devem ser efetuados para cada grupo definido por 
esses dois atributos, em conjunto. 
A consulta a seguir inclui no resultado as disciplinas que não têm inscrições e mostra como 
qualquer das formas de junções apresentadas no capítulo 5 pode ser empregada em combinação 
com as funções de agregação. 
select d.coddisciplina, 
 d.disciplina, 
 sum (i.nota) as soma_das_notas, 
 count (i.nota) as numero_de_notas, 
 avg (i.nota) as media_das_notas, 
 min (i.nota) as nota_minima, 
 max (i.nota) as nota_maxima 
from disciplina d left join inscricao i on i.coddisciplina = d.coddisciplina 
group by d.coddisciplina, d.disciplina 
order by d.disciplina 
select d.coddisciplina, 
 d.disciplina, 
 sum (i.nota) as soma_das_notas, 
 count (i.nota) as numero_de_notas, 
 avg (i.nota) as media_das_notas, 
 min (i.nota) as nota_minima, 
 max (i.nota) as nota_maxima 
from disciplina d, inscricao i 
where d.coddisciplina =(+) i.coddisciplina 
group by d.coddisciplina, d.disciplina 
order by d.disciplina 
Em função da operação de left outer join, o resultado agora inclui a disciplina Banco de Dados, 
que havia ficado de fora antes porque não possui inscrições. 
 
coddisciplina disciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 
211 Bancos de Dados NULL 0 NULL NULL NULL 
210 Compiladores 9,5 1 9,5 9,5 9,5 
112 Dir. Constitucional NULL 0 NULL NULL NULL 
114 Direito Civil 7 1 7 7 7 
117 Estatística 4,24 1 4,24 4,2 4,2 
317 Português 13,5 2 6,75 5,5 8 
316 Sociologia 13 2 6,5 3 10 
 
Note que as disciplinas Dir. Constitucional e Bancos de Dados têm, ambas, zero notas, mas por 
motivos diferentes. A primeira, porque todos seus alunos têm notas com valores nulos; a 
segunda, porque não há inscrição alguma para ela. O problema agora é escrever uma consulta 
que faça distinção desses casos, e retorne as disciplinas que ainda não têm notas registradas, 
porém possuem inscrições. 
A consulta a seguir é uma das alternativas. 
select d.disciplina, 
 count (i.matricula) as alunos_inscritos 
from disciplina d, inscricao i 
where i.coddisciplina = d.coddisciplina 
group by d.disciplina 
having count (i.nota) =0 
Error! Style not defined. 7 
 
e produz o resultado já esperado 
 
disciplina alunos_inscritos 
Dir. Constitucional 2 
 
Note que neste exemplo a função de agregação count(i.nota) é utilizada exclusivamente para 
filtrar as linhas do resultado final, pós-agregação, uma vez que o cálculo desta função não 
aparece no resultado. 
6.5 ENTENDENDO A CONSTRUÇÃO DO RESULTADO COM FUNÇÕES AGREGADAS 
O primeiro passo para entender como o SQL constrói o resultado de uma consulta que contém 
funções de agregação é tentar visualizar o resultado pré-agregação. Para isso, vamos construir 
uma consulta que produza um resultado intermediário, sobre o qual as funções de agregação vão 
atuar e computar seus resultados. A consulta que produz o resultado pré-agregação pode ser 
derivada da consulta originalmente sendo analisada, através de transformações sucessivas, tal 
que: 
1. sejam removidas as funções de agregação, permanecendo na lista-alvo as expressões sobre 
as quais essas funções são aplicadas; 
2. sejam incluídas na lista-alvo as expressões sobre as quais aplicam-se as funções de 
agregação mencionadas na cláusula order by; 
3. seja removida a cláusula order by, se existir; 
4. sejam incluídas na lista-alvo as expressões sobre as quais aplicam-se as funções de 
agregação mencionadas na cláusula having; 
5. seja removida a cláusula having, se existir; a cláusula group by deve ser substituída por uma 
cláusula order by com os mesmos atributos; 
6. a cláusula group by transforme-se numa cláusula order by. 
 
Vamos tomar como exemplo a consulta vista no início da seção 6.4. 
select i.coddisciplina, 
 d.disciplina, 
 sum (i.nota) as soma_das_notas, 
 count (i.nota) as numero_de_notas, 
 avg (i.nota) as media_das_notas, 
 min (i.nota) as nota_minima, 
 max (i.nota) as nota_maxima 
from inscricao i, disciplina d 
where i.coddisciplina = d.coddisciplina 
group by i.coddisciplina, d.disciplina 
order by d.disciplina 
 No primeiro passo, removemos as funções de agregação, mantendo na lista alvo as expressões 
sobre as quais as funções são aplicadas. Para o passo 2 não há funções de agregação na cláusula 
order by, que é removida no passo 3. Os passos 4 e 5 não alteram a consulta porque não há 
cláusula having. O passo 6 transforma a cláusula group by numa cláusula order by. A consulta 
agora é escrita como 
select i.coddisciplina, 
 d.disciplina, 
 (i.nota) as soma_das_notas, 
 (i.nota) as numero_de_notas, 
 (i.nota) as media_das_notas, 
 (i.nota) as nota_minima, 
 (i.nota) as nota_maxima 
from inscricao i, disciplina d 
where i.coddisciplina = d.coddisciplina 
order by i.coddisciplina, d.disciplina 
8 Capítulo 6: Somando, contando e agrupando 
 
e produz 
 
coddisciplina disciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 
112 Dir. ConstitucionalNULL NULL NULL NULL NULL 
112 Dir. Constitucional NULL NULL NULL NULL NULL 
114 Direito Civil 7,00 7,00 7,00 7,00 7,00 
117 Estatística 4,20 4,20 4,20 4,20 4,20 
210 Compiladores 9,50 9,50 9,50 9,50 9,50 
316 Sociologia 3,00 3,00 3,00 3,00 3,00 
316 Sociologia 10,00 10,00 10,00 10,00 10,00 
317 Português 8,00 8,00 8,00 8,00 8,00 
317 Português 5,50 5,50 5,50 5,50 5,50 
 
Este é o resultado pré-agregação. O próximo passo da construção do resultado é identificar cada 
um dos grupos de registros, separando-os a partir dos atributos coddisciplina e disciplina, e 
aplicar as funções de agregação nas colunas correspondentes. 
 
coddisciplina disciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 
112 Dir. Constitucional NULL NULL NULL NULL NULL 
 NULL NULL NULL NULL NULL 
114 Direito Civil 7,00 7,00 7,00 7,00 7,00 
117 Estatística 4,20 4,20 4,20 4,20 4,20 
210 Compiladores 9,50 9,50 9,50 9,50 9,50 
316 Sociologia 3,00 3,00 3,00 3,00 3,00 
 10,00 10,00 10,00 10,00 10,00 
317 Português 8,00 8,00 8,00 8,00 8,00 
 5,50 5,50 5,50 5,50 5,50 
coddisciplina disciplina sum count avg min max 
 
Após a aplicação das funções de agregação sobre cada coluna, de acordo com os grupos 
identificados, temos 
 
coddisciplina disciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 
112 Dir. Constitucional NULL 0 NULL NULL NULL 
114 Direito Civil 7,00 1 7,00 7,00 7,00 
117 Estatística 4,2 1 4,20 4,20 4,20 
210 Compiladores 9,5 1 9,50 9,50 9,50 
316 Sociologia 13 2 6,50 3,00 10,00 
317 Português 13,5 2 6,75 5,50 8,00 
 
Para a obtenção do resultado final, basta filtrar as linhas do resultado com o critério da cláusula 
having e ordená-lo pelos critérios da cláusula order by. 
Há casos em que as cláusulas order by e having utilizam critérios que referenciam resultados de 
funções de agregação diferentes dos que foram incluídos na lista-alvo da consulta. A título de 
exemplo, vamos supor que seja necessário descobrir quais as disciplinas que ainda têm notas 
incompletas e relacioná-las em ordem decrescente da média de suas notas. Podemos fazer isso 
listando as disciplinas onde o número de inscrições é maior que o número de notas. Note que 
nenhuma dessas contagens aparecem no resultado, sendo utilizadas exclusivamente na cláusula 
having, o mesmo ocorrendo para a média das notas, utilizada como critério de ordenação. 
select d.disciplina 
from disciplina d, inscricao i 
Error! Style not defined. 9 
 
where i.coddisciplina = d.coddisciplina 
group by d.disciplina 
having count (i.matricula) > count (i.nota) 
order by avg (i.nota) desc 
Nesta consulta, o resultado pré-agregação deveria ser gerado por uma consulta como 
select d.disciplina, 
 (i.matricula), 
 (i.nota), 
 (i.nota) 
from disciplina d, inscricao i 
where i.coddisciplina = d.coddisciplina 
order by d.disciplina 
Note que este resultado pré-agregação permite que tanto as notas como as matrículas das 
inscrições sejam contadas. Isso é necessário porque as duas colunas fazem parte do critério de 
filtragem da cláusula having. 
 
disciplina matricula nota nota 
Compiladores 1002 9,50 9,50 
Dir. Constitucional 1001 NULL NULL 
 1007 NULL NULL 
Direito Civil 1007 7,00 7,00 
Estatística 1005 4,20 4,20 
Português 1001 8,00 8,00 
 1010 5,50 5,50 
Sociologia 1005 3,00 3,00 
 1010 10,00 10,00 
disciplina count count avg 
 
A aplicação das agregações indicadas acima resulta em 
 
disciplina Count (matricula) Count (nota) Avg (nota) 
Compiladores 1 1 9,50 
Dir. Constitucional 2 0 NULL 
Direito Civil 1 1 7,00 
Estatística 1 1 4,20 
Português 2 2 6,75 
Sociologia 2 2 6,50 
 
A filtragem final permite que apenas as linhas onde 
count (i.matricula) > count (i.nota) 
permaneçam no resultado. Finalmente, a lista de disciplinas que ainda têm notas não 
preenchidas. 
 
disciplina 
Dir. Constitucional 
 
10 Capítulo 6: Somando, contando e agrupando 
 
AGREGANDO VALORES DISTINTOS 
Em algumas implementações SQL, as funções de agregação sum, avg e count podem ser 
aplicadas seletivamente, apenas sobre os diferentes valores que aparecem numa coluna. Para 
ilustrar essa característica, vamos considerar a consulta abaixo que produz, para cada curso, a 
nota média, o número de alunos inscritos, o número de inscrições e o número de notas. 
select c.curso, 
 avg (i.nota) as nota_media, 
 count (distinct i.matricula) as numero_alunos, 
 count (i.matricula) as numero_inscricoes, 
 count (i.nota) as numero_notas 
from aluno a, inscricao i, curso c 
where a.matricula = i.matricula 
 and a.codcurso = c.codcurso 
group by c.curso 
order by c.curso 
O resultado é mostrado a seguir. 
 
curso nota_media numero_alunos numero_inscricoes numero_notas 
Direito 7,5 2 4 2 
Informática 9,5 1 1 1 
Jornalismo 5,67499995231628 2 4 4 
 
O número de alunos e o número de inscrições são diferentes em dois cursos, embora a mesma 
função de agregação tenha sido utilizada sobre a mesma coluna. A diferença é que, no caso do 
número de alunos, a contagem foi efetuada sobre valores distintos. No caso do curso de Direito, 
há duas matrículas diferentes, embora cada uma delas apareça duas vezes. O resultado pré-
agregação desta consulta é 
 
curso nota_media numero_alunos numero_inscricoes numero_notas
Direito NULL 1001 1001 NULL 
 8,00 1001 1001 8,00 
 NULL 1007 1007 NULL 
 7,00 1007 1007 7,00 
Informática 9,50 1002 1002 9,50 
Jornalismo 3,00 1005 1005 3,00 
 4,20 1005 1005 4,20 
 5,50 1010 1010 5,50 
 10,00 1010 1010 10,00 
curso avg count distinct count count 
 
AGREGANDO COM COUNT(*) 
A função agregada count, como mostrada até aqui, fica suscetível à ocorrência de valores nulos, 
pois esses não são computados. Quando é necessário fazer uma consulta que conte todos os 
valores separadamente, mesmo que sejam nulos, é possível usar a construção count(*), que tem 
o significado de “contar linhas”. Uma consulta que apenas contasse as inscrições, por 
disciplina, poderia ser escrita como 
select d.disciplina, 
 count (*) as numero_de_inscricoes 
from disciplina d, inscricao i 
Error! Style not defined. 11 
 
where d.coddisciplina = i.coddisciplina 
group by d.disciplina 
order by 1 
Pode-se imaginar o resultado pré-agregação como se a coluna onde a função count (*) é aplicada 
fosse formada por todos os atributos que não são pivôs da cláusula group by. Ou seja, a função 
contaria efetivamente o número de combinações de registros que correspondem a cada diferente 
valor do atributo disciplina. 
 
 
disciplina d.coddisciplina, d.codprofessor, d.chst, d.chsp, i.matricula, i.coddsciplina, 
Compiladores ... 
Dir. Constitucional ... 
 ... 
Direito Civil ... 
Estatística ... 
Português ... 
 ... 
Sociologia ... 
 ... 
disciplina count (*) 
 
O resultado produzido é 
 
disciplina numero_de_inscricoes
Compiladores 1 
Dir. Constitucional 2 
Direito Civil 1 
Estatística 1 
Português 2 
Sociologia 2 
 
Note que a consulta acima produz o mesmo resultado que 
select d.disciplina, 
 sum (1) as numero_de_inscricoes 
from disciplina d, inscricao i 
where d.coddisciplina = i.coddisciplina 
group by d.disciplina 
order by 1 
porque o resultado pré-agregação, obtido a partir da consulta 
select d.disciplina, 
 (1) as numero_de_inscricoes 
from disciplina d, inscricao i 
where d.coddisciplina = i.coddisciplina 
é 
disciplina numero_de_inscricoes
Dir. Constitucional 1 
 1 
Direito Civil 1 
Estatística 1 
Compiladores 1 
Sociologia 1 
12 Capítulo 6: Somando, contando e agrupando 
 
 1 
Português 1 
 1 
disciplina sum 
CALCULANDO COM FUNÇÕES DE AGREGAÇÃO 
Funções de agregação podem ser utilizadas em expressões aritméticas na lista-alvo. Vamos 
considerar um exemplo onde é necessário produzir, para cada aluno, a média de suas notas 
ponderada pelo número de créditos de cada disciplina. Ou seja, as disciplinas com mais créditos 
pesam mais no cômputo da média.Os créditos são calculados a partir da carga horária de cada 
disciplina. Em SQL não existe a noção de média ponderada e é preciso calculá-la 
expressamente. A consulta 
select a.nome, 
 sum (i.nota * (d.chst + d.chsp * 0.5)) / sum (d.chst + d.chsp * 0.5) as media_ponderada 
from aluno a, inscricao i, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
group by a.nome 
order by a.nome 
faz isso e produz como resultado 
 
nome media_ponderada 
Barbara Carlito 3,554 
Carlos Maradona 3,9 
Maria Lucia Silva 7,6 
Maria Rita Colatti 9,5 
Ricardo Biondi 4 
 
É interessante analisar o resultado pré-agregação desta consulta e verificar o que acontece no 
seu processamento. 
select a.nome, 
 (i.nota * (d.chst + d.chsp * 0.5)) as notas_creditos, 
 (d.chst + d.chsp * 0.5) as media_ponderada 
from aluno a, inscricao i, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 order by a.nome 
Na base de exemplos, a aluna Barbara Carlito está inscrita em duas disciplinas, Sociologia e 
Estatística, de 3,5 (3�1+1�0,5) e 3 (2�1+2�0,5) créditos, respectivamente. Barbara tem notas 
3 e 4,2 nessas disciplinas. Assim, na coluna referente à aplicação da função sum (i.nota * (d.chst + 
d.chsp * 0.5)), aparecem os valores 10,5 e 12,6, que correspondem a 3 � 3,5 e 4,2 � 3. O valor de 
sum (i.nota * (d.chst + d.chsp * 0.5)) para essa aluna é 23,1 e o valor de sum (d.chst + d.chsp * 0.5) é 6,5. 
Logo, a média é 3,554, correspondente ao quociente 23,1 / 6,5. 
 
nome notas_creditos media_ponderada
Barbara Carlito 10,5 3,5 
Barbara Carlito 12,6 3 
Carlos Maradona 35 5 
Carlos Maradona 4 
Maria Lucia Silva 22 4 
Maria Lucia Silva 35 3,5 
Maria Rita Colatti 38 4 
Error! Style not defined. 13 
 
Ricardo Biondi 32 4 
Ricardo Biondi 4 
 
O aluno Carlos Maradona tem uma inscrição em Dir. Constitucional, de 4 créditos, sem nota 
(valor nulo) e outra inscrição em Direito Civil, de 5 créditos, com nota 7. A média é a divisão 
de 35 por 9, porque a nota nula não contribui para o numerador da média. Contudo, o número 
de créditos da disciplina sem nota foi computado no denominador. No capítulo 7, este exemplo 
será revisitado, pois com a ajuda de uma função será possível eliminar do denominador os 
créditos dessa disciplina sem nota. 
FUNÇÕES DE AGREGAÇÃO E A CLÁUSULA WHERE 
Um engano bastante freqüente de programadores SQL iniciantes é o de tentar utilizar funções de 
agregação em termos lógicos da cláusula where. Vamos ilustrar esse caso com uma consulta 
que requer a lista de alunos cujas notas sejam maiores ou iguais à média geral das notas. 
Intuitivamente, muitos programadores tentariam resolver esta consulta como 
select a.nome, 
 d.disciplina, 
 i.nota 
from aluno a, inscricao i, disciplina d 
where a.matricula=i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and i.nota >= avg (d.nota) 
order by 1 
Esta construção não é aceita, porque fica impossível obter a consulta pré-agregação. Os termos 
lógicos da cláusula where são avaliados para cada combinação das instâncias das variáveis de 
registro SQL. Neste momento, não há colunas formadas ainda e a função de agregação não 
pode ser aplicada. 
No capítulo 9 esta consulta é formulada através da utilização de subconsultas SQL. Por ora, fica 
estabelecida uma regra básica: funções de agregação não podem aparecer em termos lógicos na 
cláusula where, jamais! 
6.6 CARACTERÍSTICAS DAS IMPLEMENTAÇÕES 
As seções a seguir apresentam diferentes aspectos na implementação de funções de agregação e 
construções correlatas. 
ROLL UP E CUBE 
O Oracle e o SQL Server dispõem de dois construtores, rollup e cube, que podem ser utilizados 
em conjunto com as funções de agregação. Para ilustrar seus efeitos, vamos considerar a 
consulta 
select c.curso, d.disciplina, 
 avg (i.nota) as nota_media, 
 count (i.matricula) as numero_inscricoes, 
 count (i.nota) as numero_notas 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by c.curso, d.disciplina 
order by c.curso, d.disciplina 
que produz 
X
14 Capítulo 6: Somando, contando e agrupando 
 
 
curso disciplina nota_media numero_inscricoes numero_notas 
Direito Dir. Constitucional NULL 2 0 
Direito Direito Civil 7,0 1 1 
Direito Português 8,0 1 1 
Informática Compiladores 9,5 1 1 
Jornalismo Estatística 4,2 1 1 
Jornalismo Português 5,5 1 1 
Jornalismo Sociologia 6,5 2 2 
 
Nesta formulação, as funções de agregação são aplicadas sobre os grupos de registros formados 
por cursos e disciplinas. A mesma consulta, com a cláusula rollup, 
select c.curso, d.disciplina, 
 avg (i.nota) as nota_media, 
 count (i.matricula) as numero_inscricoes, 
 count (i.nota) as numero_notas 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by c.curso, d.disciplina with rollup 
order by c.curso, d.disciplina 
select c.curso, d.disciplina, 
 avg (i.nota) as nota_media, 
 count (i.matricula) as numero_inscricoes, 
 count (i.nota) as numero_notas 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by rollup (c.curso, d.disciplina) 
order by c.curso, d.disciplina 
produz resultado semelhante, porém computadas também as funções de agregação para cada 
grupo de disciplinas e para o conjunto de registros. Oracle e SQL Server apresentam leves 
diferenças sintáticas na formulação das consultas. 
 
 
curso disciplina nota_media numero_inscricoes numero_notas 
NULL NULL 6.74286 9 7 
Direito NULL 7.5 4 2 
Direito Dir. Constitucional NULL 2 0 
Direito Direito Civil 7.0 1 1 
Direito Português 8.0 1 1 
Informática NULL 9.5 1 1 
Informática Compiladores 9.5 1 1 
Jornalismo NULL 5.67542 4 4 
Jornalismo Estatística 4.2 1 1 
Jornalismo Português 5.5 1 1 
Jornalismo Sociologia 6.5 2 2 
 
O efeito da opção rollup é o de introduzir totais e subtotais no resultado da consulta. De fato, 
foram incluídos mais dois níveis de agregação na consulta: por curso e geral. A nota média, o 
número de inscrições e o número de notas são calculados também, para todas as disciplinas de 
cada curso e para todas as discplinas de todos os cursos. Para o curso de Jornalismo, a média 
geral das notas é 5,67542. Este nível de agregação não aparece na consulta de agregação sem o 
Agregações para 
todos os cursos e 
todas as disciplinas 
Agregações por 
curso/disciplina
Agregações por 
curso para todas 
as disciplinas 
Error! Style not defined. 15 
 
construtor rollup. Assim, na coluna disciplina, o significado do valor nulo seria Todas e, na 
coluna curso, o significado do valor seria também Todos. 
Com a cláusula cube, 
select c.curso, d.disciplina, 
 avg (i.nota) as nota_media, 
 count (i.matricula) as numero_inscricoes, 
 count (i.nota) as numero_notas 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by c.curso, d.disciplina with cube 
order by c.curso, d.disciplina 
select c.curso, d.disciplina, 
 avg (i.nota) as nota_media, 
 count (i.matricula) as numero_inscricoes, 
 count (i.nota) as numero_notas 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by cube (c.curso, d.disciplina) 
order by c.curso, d.disciplina 
o resultado é semelhante, porém acrescido dos totais gerais para cada disciplina. Cada uma das 
linhas onde uma disciplina aparece como valor nulo na coluna curso deve ser lida como os 
valores agregados daquela disciplina para todos os cursos. 
 
curso disciplina nota_media numero_inscricoes numero_notas 
NULL NULL 6,743 9 7 
NULL Compiladores 9,5 1 1 
NULL Dir. Constitucional NULL 2 0 
NULL Direito Civil 7,0 1 1 
NULL Estatística 4,2 1 1 
NULL Português 6,75 2 2 
NULL Sociologia 6,5 2 2 
Direito NULL 7,5 4 2 
Direito Dir. Constitucional NULL 2 0 
Direito Direito Civil 7,0 1 1 
Direito Português 8,0 1 1 
Informática NULL 9,5 1 1 
Informática Compiladores 9,5 1 1 
Jornalismo NULL 5,675 4 4 
Jornalismo Estatística 4,2 1 1 
Jornalismo Português 5,5 1 1 
Jornalismo Sociologia 6,5 2 2 
 
É possível substituir o valor nulo, que aparece nas totalizações, por literais mais adequados. 
Isso pode ser feito com o emprego da função de agregação grouping. Cada vez que uma linha é 
adicionada ao resultado, a função grouping indica se a mesma é provocada pelo efeito das 
opções de totalização e, com isso, pode-se usar valores diferentes de nulo no resultado. O 
resultado da consulta 
select c.curso, d.disciplina, 
 avg (i.nota) as nota_media, 
 count (i.matricula) as numero_inscricoes, 
 count (i.nota) as numero_notas, 
 grouping (c.curso) as grouping_curso, 
 grouping (d.disciplina) as grouping_disciplina 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
Agregações 
por disciplina 
para todos os 
cursos 
16 Capítulo 6: Somando, contando e agrupando 
 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by c.curso, d.disciplina with rollup 
order by c.curso, d.disciplina 
select c.curso, d.disciplina, 
 avg (i.nota) as nota_media, 
 count (i.matricula) as numero_inscricoes, 
 count (i.nota) as numero_notas, 
 grouping (c.curso) as grouping_curso, 
 grouping (d.disciplina) as grouping_disciplina 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by rollup (c.curso, d.disciplina) 
order by c.curso, d.disciplina 
mostra esse efeito. 
 
curso disciplina nota_media numero_inscricoes numero_notas grouping_curso grouping_disciplina 
NULL NULL 6,743 9 7 1 1 
Direito NULL 7,5 4 2 0 1 
Direito Dir. Constitucional NULL 2 0 0 0 
Direito Direito Civil 7,0 1 1 0 0 
Direito Português 8,0 1 1 0 0 
Informática NULL 9,5 1 1 0 1 
Informática Compiladores 9,5 1 1 0 0 
Jornalismo NULL 5,675 4 4 0 1 
Jornalismo Estatística 4,2 1 1 0 0 
Jornalismo Português 5,5 1 1 0 0 
Jornalismo Sociologia 6,5 2 2 0 0 
 
Note que, cada vez que as colunas curso ou disciplina são totalizadas, a coluna de grouping 
correspondente assume o valor 1. Esses valores podem ser utilizados para transformar o 
conteúdo daquelas colunas, de modo a tornar mais compreensível o resultado. 
 
select case when grouping (c.curso)=1 then 'Todos cursos' 
 else c.curso end as curso, 
 case when grouping (d.disciplina)=1 then 'Todas disciplinas' 
 else d.disciplina end as disciplina, 
 avg (i.nota) as nota_media, 
 count (i.matricula) as numero_inscricoes, 
 count (i.nota) as numero_notas 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by c.curso, d.disciplina with rollup 
order by c.curso, d.disciplina 
 
select decode ( grouping (c.curso), 1, 'Todos cursos', c.curso) as curso, 
 decode ( grouping (d.disciplina), 1, 'Todas disciplinas', d.disciplina) as disciplina, 
 avg (i.nota) as nota_media, 
 count (i.matricula) as numero_inscricoes, 
 count (i.nota) as numero_notas 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by rollup (c.curso, d.disciplina) 
order by c.curso, d.disciplina 
 
curso disciplina nota_media numero_inscricoes numero_notas 
Error! Style not defined. 17 
 
Direito Dir. Constitucional NULL 2 0 
Direito Direito Civil 7,0 1 1 
Direito Português 8,0 1 1 
Direito Todas disciplinas 7,5 4 2 
Informática Compiladores 9,5 1 1 
Informática Todas disciplinas 9,5 1 1 
Jornalismo Estatística 4,2 1 1 
Jornalismo Português 5,5 1 1 
Jornalismo Sociologia 6,5 2 2 
Jornalismo Todas disciplinas 5,675 4 4 
Todos cursos Todas disciplinas 6,743 9 7 
 
Agora, nas linhas que totalizam os cursos aparecem os dizeres “Todas disciplinas” e nas linhas 
que totalizam as disciplinas, “Todos cursos”. 
A CLÁUSULA COMPUTE NO SQL SERVER 
A cláusula compute no SQL Server oferece a possibilidade de extrair totais de controle para 
consultas. Considere o seguinte exemplo. 
select c.curso, d.disciplina,i.nota 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
order by c.curso, d.disciplina 
Esta consulta produz uma lista de cursos, disciplinas e notas, como mostrado abaixo. 
curso disciplina nota 
Direito Dir. Constitucional NULL 
Direito Dir. Constitucional NULL 
Direito Direito Civil 7,0 
Direito Português 8,0 
Informática Compiladores 9,5 
Jornalismo Estatística 4,2 
Jornalismo Português 5,5 
Jornalismo Sociologia 10,0 
Jornalismo Sociologia 3,0 
Suponha que seja interessante conhecer dados adicionais, como os valores de média, máximo e 
mínimo das notas. A cláusula compute pode ser empregada neste caso. 
select c.curso, d.disciplina,i.nota 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
order by c.curso, d.disciplina 
compute avg (i.nota), max(i.nota), min(i.nota) 
O resultado de uma consulta com essa é um pouco diferente dos demais vistos até aqui, uma vez 
que são produzidos mais de um conjunto de registros no resultado (recordsets), como aparece a 
seguir. 
curso disciplina nota 
Direito Dir. Constitucional NULL 
Direito Dir. Constitucional NULL 
Direito Direito Civil 7,0 
Direito Português 8,0 
Informática Compiladores 9,5 
Jornalismo Estatística 4,2 
Jornalismo Português 5,5 
Jornalismo Sociologia 10,0 
18 Capítulo 6: Somando, contando e agrupando 
 
Jornalismo Sociologia 3,0 
 
avg max min 
6,74 10,0 3,0 
Considere agora que essa estatísticas devem ser fornecidas para cada um dos cursos. Neste 
caso, pode-se empregar a cláusula compute by, que tem função semelhante à que foi 
apresentada acima. 
select c.curso, d.disciplina,i.nota 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
order by c.curso, d.disciplina 
compute avg (i.nota), max(i.nota), min(i.nota) by c.curso 
O resultado agora compõe-se de seis recordsets, cada par referente a um dos três diferentes 
cursos. 
curso disciplina nota 
Direito Dir. Constitucional NULL 
Direito Dir. Constitucional NULL 
Direito Direito Civil 7,0 
Direito Português 8,0 
 
avg max min 
7,5 8,0 7,0 
 
curso disciplina nota 
Informática Compiladores 9,5 
 
avg max min 
9,5 9,5 9,5 
 
curso disciplina nota 
Jornalismo Estatística 4,2 
Jornalismo Português 5,5 
Jornalismo Sociologia 10,0 
Jornalismo Sociologia 3,0 
 
avg max min 
5,67 10,0 3,0 
Uma variação adicional poderia, ainda, ser escrita como 
select c.curso, d.disciplina,i.nota 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
order by c.curso, d.disciplina 
compute avg (i.nota), max(i.nota), min(i.nota) by c.curso 
compute avg (i.nota), max(i.nota), min(i.nota) 
Neste caso, seria produzido o resultado acima acrescentado das estatísticas globais, isto é, paratodos os registros de todos os cursos. No total, haveria sete recordsets. 
A cláusula compute é mantida por questões de compatibilidade com versões anteriores do 
SQL Server. Efeito semelhante pode ser obtido pelas cláusulas roll up ou até mesmo pela 
Error! Style not defined. 19 
 
união de consultas com e sem a cláusula group by. 
FUNÇÕES DE AGREGAÇÃO NO ACCESS 
As seguintes funções de agregação estão disponíveis no Access: 
 
Função Nome em português2 Comentários 
avg Média Calcula a média aritmética dos valores. Não inclui valores nulos no cálculo. 
count Contagem Contagem de valores. Valores nulos não são contados a menos que o caracter 
* (asterisco) seja usado como parâmetro. 
count_big Contagem Idêntica à função count, exceto que retorna valores do tipo bigint. 
min Mín Retorna o mínimo dentre os valores. Ignora valores nulos. 
max Máx Retorna o máximo dentre os valores. Ignora valores nulos. 
stdev Desv Calcula o desvio padrão dos valores. Retorna nulo se houver menos de dois 
valores. 
stdevp DesvP Calcula o desvio padrão de uma amostra dos valores. Retorna nulo se houver 
menos de um valor. 
sum Soma Calcula o somatório dos valores. Valores nulos são ignorados. 
var Var Calcula a variância dos valores. Retorna nulo se houver menos de dois 
valores. 
varp VarP Calcula a variância de uma amostra dos valores. Retorna nulo se houver 
menos de dois valores. 
 
A cláusula pivot e a instrução transform, no Access, permitem a construção de consultas que 
produzem resultados agregados bastante interessantes. A idéia básica é transformar os valores 
de um determinado atributo, ou expressão, em colunas e, a partir daí, aplicar as funções de 
agregação sobre os valores desejados. A consulta 
transform avg (i.nota) as nota_media 
select c.curso 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by c.curso 
order by c.curso 
pivot d.disciplina 
produz 
 
curso Compiladores Dir_ Constitucional Direito Civil Estatística Português Sociologia
Direito 7,0 8,0 
Informática 9,5 
Jornalismo 4,2 5,5 6,5 
 
O resultado é uma tabela de referência cruzada. O atributo disciplina, da tabela DISCIPLINA, foi 
declarado como pivô na última linha da consulta, fazendo com que os valores deste atributo 
aparecessem como colunas no resultado final. Para cada combinação do pivô com o atributo 
 
2 Na versão em Português, o Access traduz os nomes das funções de agregação quando as 
apresenta na interface gráfica de consultas. 
20 Capítulo 6: Somando, contando e agrupando 
 
objeto da cláusula group by, são produzidos os resultados da função de agregação declarada na 
instrução transform. Note que, para a disciplina Dir. Constitucional, o nome foi levemente 
alterado porque o caracter “.” não é permitido num nome de coluna. 
O mesmo exemplo, porém com as linhas e colunas invertidas, pode ser obtido pela consulta 
transform avg (i.nota) as nota_media 
select d.disciplina 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by d.disciplina 
order by c.curso 
pivot c.curso 
que produz 
 
disciplina Direito Informática Jornalismo
Compiladores 9,5 
Dir. Constitucional 
Direito Civil 7,0 
Estatística 4,2 
Português 8,0 5,5 
Sociologia 6,5 
 
A cláusula pivot, com a opção in, pode ser utilizada tanto para limitar os valores que aparecem 
nas colunas como para determinar valores que devem aparecer sempre, mesmo que não constem 
do banco de dados. Na consulta abaixo, a cláusula pivot determina que devem aparecer sempre 
os cursos Direito, Jornalismo e Física, embora este último não esteja presente na base de dados. 
transform avg (i.nota) as nota_media 
select d.disciplina 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by d.disciplina 
order by c.curso 
pivot c.curso in ('Direito','Jornalismo','Física') 
Todos os valores para o curso de Física são, previsivelmente, nulos, mas a coluna 
obrigatoriamente aparece. 
disciplina Direito Jornalismo Física 
Compiladores 
Dir. Constitucional 
Direito Civil 7,0 
Estatística 4,2 
Português 8,0 5,5 
Sociologia 6,5 
Normalmente, a utilização da função transform deve ser acompanhada da cláusula group by. 
Sendo assim, como é possível obter as agregações sobre algum atributo pivô sem ter que 
vinculá-las a outro atributo? A consulta 
transform avg (i.nota) as nota_media 
select null as nulo 
from aluno a, inscricao i, curso c, disciplina d 
where a.matricula = i.matricula 
 and i.coddisciplina = d.coddisciplina 
 and a.codcurso = c.codcurso 
group by null 
order by c.curso 
pivot c.curso 
Error! Style not defined. 21 
 
faz isto. Note que o valor nulo foi utilizado como referência, tanto na cláusula select como na 
cláusula group by. O efeito final é mostrado a seguir. 
 
Nulo Direito Informática Jornalismo 
NULL 7,5 9,5 5,675 
 
A obtenção de efeitos semelhantes é extremamente trabalhosa em SQL. 
FUNÇÕES DE AGREGAÇÃO NO MYSQL 
As seguintes funções de agregação estão disponíveis no MySQL: 
 
Função Comentários 
avg Calcula a média aritmética dos valores. Não inclui valores nulos no cálculo. 
bit_or Retorna o resultado do operador lógico or sobre bits dos valores. 
bit_and Retorna o resultado do operador lógico and sobre bits dos valores. 
count Contagem de valores. Valores nulos não são contados a menos que o caracter * (asterisco) seja 
usado como parâmetro. Pode ser utilizada em combinação com a opção distinct. 
min Retorna o mínimo dentre os valores. Ignora valores nulos. 
max Retorna o máximo dentre os valores. Ignora valores nulos. 
sdt, stddev Calcula o desvio padrão dos valores. Os dois nomes são sinônimos. 
sum Calcula o somatório dos valores. Valores nulos são ignorados. 
variance Calcula a variância dos valores. Retorna nulo se houver menos de dois valores. 
 
Na função agregada count é possível utilizar mais de uma coluna como parâmetro, na forma 
count (c1, c2,...). Neste caso, a contagem retorna o número de combinações não nulas nas 
colunas referenciadas. Nas demais implementações, efeito semelhante pode ser obtido pela 
concatenação das diferentes colunas. 
As funções agregadas bit_or e bit_and atuam sobre os bits dos valores. Cada valor é convertido 
para um inteiro de 64 bits e é então operado. Tipos que não admitem conversão para inteiros 
não são aceitos como operandos. A consulta 
select bit_or (chst) as disjuncao, 
 bit_and (chst) as conjuncao 
from disciplina 
resulta em 
 
disjuncao conjuncao 
7 0 
 
porque 4|4|2|2|3|3|4 = 7 e 4&4&2&2&3&3&4 = 0. Os caracteres | e & representam os 
operadores or e and sobre bits. 
Em alguns casos, MySQL permite que seja omitida a referência a algum item da cláusula group 
by. Observe a consulta 
select d.disciplina, 
 p.nome, 
 avg (i.nota) as nota_media 
from disciplina d, professor p, inscricao i 
22 Capítulo 6: Somando, contando e agrupando 
 
where i.coddisciplina = d.coddisciplina 
 and d.codprofessor = p.codprofessor 
group by d.disciplina 
order by 1 
e seu resultado 
 
disciplina nome nota_media 
Compiladores Lenira Rocha 9,5 
Dir. Constitucional Zenubio Siqueira NULL 
Direito Civil Olivia Straw 7 
Estatística Pedro Amarante 4,2 
Português Carlos Azambuja 6,75 
Sociologia Silvia Ferreira 6,5 
 
Normalmente, esta consulta provocaria um erro de compilação, uma vez que a expressão 
p.nome, incluída na lista-alvo, não é mencionada na cláusula group by. Entretanto, o MySQL 
permite essa construção desde que cada valor da expressão não mencionada na cláusula group 
by possua um único valor para cada grupo que o envolve.No caso, o nome do professor é único 
dentro do grupo de uma disciplina. 
No MySQL é possível especificar a ordem em que os grupos são apresentados numa consulta 
com a cláusula group by. A consulta 
select d.disciplina, 
 avg (i.nota) as nota_media 
from disciplina d, inscricao i 
where i.coddisciplina = d.coddisciplina 
group by d.disciplina desc 
produz um resultado com os nomes das disciplinas dispostos em ordem decrescente. 
 
disciplina nota_media 
Sociologia 6,5 
Português 6,75 
Estatística 4,2 
Direito Civil 7 
Dir. Constitucional NULL 
Compiladores 9,5 
 
A cláusula group by pode combinar seus operandos com as palavras-chave asc e desc. 
FUNÇÕES DE AGREGAÇÃO NO ORACLE 
As seguintes funções de agregação estão disponíveis no Oracle: 
 
Função Comentários 
avg Calcula a média aritmética dos valores. Não inclui valores nulos no cálculo. Pode ser utilizada em 
combinação com a opção distinct. 
count Contagem de valores. Valores nulos não são contados a menos que o caracter * (asterisco) seja 
usado como parâmetro. Pode ser utilizada em combinação com a opção distinct. 
grouping Causa uma coluna adicional no resultado que é 1 quando uma linha está sendo adicionada pelo efeito 
das cláusulas CUBE ou ROLLUP e 0 caso contrário. 
min Retorna o mínimo dentre os valores. Ignora valores nulos. Pode ser utilizada em combinação com a 
opção distinct. 
max Retorna o máximo dentre os valores. Ignora valores nulos. Pode ser utilizada em combinação com a 
Error! Style not defined. 23 
 
opção distinct. 
stddev Calcula o desvio padrão dos valores. Os dois nomes são sinônimos. Pode ser utilizada em 
combinação com a opção distinct. 
sum Calcula o somatório dos valores. Valores nulos são ignorados. Pode ser utilizada em combinação com 
a opção distinct. 
variance Calcula a variância dos valores. Retorna nulo se houver menos de dois valores. Pode ser utilizada em 
combinação com a opção distinct. 
O Oracle oferece algumas variações na escrita da cláusula group by, como mostra a sintaxe a 
seguir. 
<cláusula group by> := 
 group by { <simple grouping clause> | <grouping sets clause> },... having <expressão lógica> 
<simple grouping clause> := 
 { <expressão> 
 | ( <expressão>,... ) 
 | { cube | rollup } ( { <expressão> | ( <expressão>,... ) },... ) 
 | ( ) 
} 
<grouping sets clause> := 
 groupinp sets ( <simple grouping clause>,... ) 
A cláusula grouping sets permite que o programador defina as combinações de expressões que 
devem ser agrupadas, evitando-se a formação de grupos que não são interessantes. Por 
exemplo, considere a consulta a seguir, apresentada em exemplo anterior. 
select d.disciplina, 
 count (*) as numero_de_inscricoes 
from disciplina d, inscricao i 
where d.coddisciplina = i.coddisciplina 
group by d.disciplina 
order by 1 
Neste caso o grupamento é sempre o mesmo, ou seja, cada disciplina existente dentre as 
inscrições formará um grupo de agregação. Com o auxílio de grouping sets, esta consulta 
poderia reescrita como 
select d.disciplina, i.matricula, 
 count (*) as numero_de_inscricoes 
from disciplina d, inscricao i 
where d.coddisciplina = i.coddisciplina 
group by grouping sets ( d.disciplina, i.matricula ) 
order by 1 nulls first, 2 
fazendo com que sejam agregados os valores com relação a mais de uma referência, como 
mostra o seu resultado. 
disciplina matricula numero_de_inscricoes 
 1001 2 
 1002 1 
 1005 2 
 1007 2 
 1010 2 
Compiladores 1 
Dir. Constitucional 2 
Direito Civil 1 
Estatística 1 
Português 2 
Sociologia 2 
O mesmo resultado poderia ser obtido com a união de duas consultas de agregação simples, 
como no caso a seguir. 
24 Capítulo 6: Somando, contando e agrupando 
 
select d.disciplina, null as matricula, 
 count (*) as numero_de_inscricoes 
from disciplina d, inscricao i 
where d.coddisciplina = i.coddisciplina 
group by d.disciplina, null 
union 
select null as disciplina, i.matricula, 
 count (*) as numero_de_inscricoes 
from disciplina d, inscricao i 
where d.coddisciplina = i.coddisciplina 
group by null, i.matricula 
order by 1 nulls first, 2 
∴ 
Em certas situações, o Oracle oferece uma possibilidade interessante que é a de aplicar uma 
função de agregação sobre o resultado de outra função de agregação. Parece complicado, mas 
os exemplos a seguir ilustram essa possibilidade. 
A consulta 
select avg (nota) as media_por_aluno 
from inscricao 
group by matricula 
retorna a lista de médias de notas dos alunos, separadas pelas matrículas dos alunos. Ou seja, 
cada média da lista é a média das notas de um aluno, como é mostrado a seguir. 
 
media_por_aluno 
8 
9,5 
3,6 
7 
7,75 
 
É possível aplicar uma outra função de agregação sobre este resultado. Por exemplo, para 
retornar a maior média entre todas, pode-se escrever 
select max (avg (nota)) as media_maxima 
from inscricao 
group by matricula 
com o seguinte resultado 
media_maxima 
9,5 
Outras funções de agregação podem ser utilizadas. Para obtermos a média das médias, bastaria 
construir uma consulta como 
select avg (avg (nota)) as media_das_medias 
from inscricao 
group by matricula 
produzindo 
media_das_medias 
6,75 
Note que a construção da consulta é toda voltada para a função de agregação mais interna, 
sendo que a função mais externa trabalha no resultado consolidado da primeira função. Esse 
conceito pode ser ilustrado com uma consulta que conta o número de médias produzidos. 
select count (avg (nota)) 
Error! Style not defined. 25 
 
from inscricao 
group by matricula 
O resultado é 5. Porém se incluirmos uma cláusula having nessa mesma consulta, filtrando as 
médias maiores que 4, como na consulta 
select count (avg (nota)) as contagem_de_medias 
from inscricao 
group by matricula 
having avg (nota) > 4 
o resultado passa a ser 
contagem_de_medias 
4 
Note que o filtro ocasionado pela cláusula having aplica-se na formação das médias, e não na 
contagem final. Referências à função count na cláusula having, neste caso, não são aceitas, 
provocando erros de sintaxe. Uma consulta como 
select count (avg (nota)) as contagem_de_medias 
from inscricao 
group by matricula 
having count(avg (nota)) > 4 
não pode ser processada pelo Oracle. 
 
FUNÇÕES DE AGREGAÇÃO NO SQL SERVER 
As seguintes funções de agregação estão disponíveis no SQL Server: 
 
Função Comentários 
avg Calcula a média aritmética dos valores. Não inclui valores nulos no cálculo. Pode ser utilizada em 
combinação com a opção distinct. 
count Contagem de valores. Valores nulos não são contados a menos que o caracter * (asterisco) seja 
usado como parâmetro. Pode ser utilizada em combinação com a opção distinct. 
grouping Causa uma coluna adicional no resultado que é 1 quando uma linha está sendo adicionada pelo efeito 
das cláusulas CUBE ou ROLLUP e 0 caso contrário. 
max Retorna o mínimo dentre os valores. Ignora valores nulos. 
min Retorna o máximo dentre os valores. Ignora valores nulos. 
stdev Calcula o desvio padrão dos valores. Retorna nulo se houver menos de dois valores. 
stdevp Calcula o desvio padrão de uma amostra dos valores. Retorna nulo se houver menos de um valor. 
sum Calcula o somatório dos valores. Valores nulos são ignorados. Pode ser utilizada em combinação com 
a opção distinct. 
var Calcula a variância dos valores. Retorna nulo se houver menos de dois valores. 
varp Calcula a variância de uma amostra dos valores. Retorna nulo se houver menos de dois valores. 
A opção distinct somente pode ser utilizada sobre colunas simples, sem expressões. 
 
 
X
26 Capítulo 6: Somando, contando e agrupando 
 
EXERCÍCIOS 
6.1 Escreva uma consulta SQL que produza a lista dos três alunos com as melhores médias, em 
ordem decrescente, produzindo o resultado abaixo. 
aluno media 
Maria Rita Colatti 9,5 
Ricardo Biondi 8 
Maria Lucia Silva 7,75 
6.2 Escreva uma consulta SQL que forneça, paracada professor, a menor nota, a maior nota, a 
média e o desvio padrão das notas. O resultado esperado, em ordem alfabética, é mostrado a 
seguir. 
 
nome menor_nota maior_nota media desvio_padrao 
Carlos Azambuja 5,5 8 6,75 1,76776695296637 
Lenira Rocha 9,5 9,5 9,5 
Olivia Straw 7 7 7 
Pedro Amarante 4,2 4,2 4,2 
Silvia Ferreira 3 10 6,5 4,94974746830583 
Zenubio Siqueira 
6.3 Escreva uma consulta SQL, no Access, que produza uma tabela de referência cruzada 
contendo o desempenho dos alunos de cada curso, por sexo. Utilize a média geral de cada 
aluno como critério. 
curso F M 
Direito 7,5 
Informática 9,5 
Jornalismo 5,675 
6.4 Considere novamente a instância da tabela ALUNO após a inclusão de homônimos, como foi 
feito num exemplo do capítulo 4. 
Matricula Nome Sexo CodCurso Nascimento 
1001 Ricardo Biondi M DIR 21/02/80 
1002 Maria Rita Colatti F INF 10/11/78 
1004 Oscarito Vianna M DIR 14/08/79 
1005 Barbara Carlito F JOR 29/10/79 
1007 Carlos Maradona M DIR 30/06/77 
1008 Sacadura Miranda M INF 12/12/81 
1010 Maria Lucia Silva F JOR 10/08/75 
1014 Maria Aparecida da Silva F JOR 24/09/77 
1015 Oscarito Vianna M INF 12/05/79 
1017 Maria Aparecida da Silva F INF 07/04/78 
Escreva uma consulta SQL que indique, para cada nome que tenha algum homônimo, em 
ordem alfabética, o número de ocorrências do mesmo, produzindo o resultado abaixo. 
nome ocorrencias 
Maria Aparecida da Silva 2 
Oscarito Vianna 2 
6.5 Escreva uma consulta SQL que forneça o número de possíveis inscrições na base de dados, 
isto é, o número de combinações de alunos e disciplinas, como abaixo. 
possiveis_inscricoes 
49 
6.6 Observe a tabela mostrada abaixo, que registra o resultado de um páreo no Jockey Clube. 
Error! Style not defined. 27 
 
Cavalo Tempo 
Estupenda 159,25 
Muddy River 160,42 
Golden Champ 157,63 
Easy Rider 159,70 
Escreva uma consulta SQL que produza o ranking numerado desse páreo, apresentando os 
cavalos em ordem de chegada. 
ranking cavalo 
1 Golden Champ 
2 Estupenda 
3 Easy Rider 
4 Muddy River 
Tente resolver o problema sem usar funções que retornem o número da linha diretamente, 
como faz a função rownum, no Oracle. 
6.7 Considere novamente o banco de dados do exercício 5.5 e escreva uma consulta SQL que 
produza o número de carros que estão equipados com cada um dos diversos acessórios. O 
resultado deve ser o que é mostrado a seguir. 
acessorio numero_de_carros 
Câmbio automático 1 
CD-Player 4 
Direção hidráulica 4 
Rodas de liga leve 4 
Vidros elétricos 4 
6.8 Indique o resultado para cada uma das consultas abaixo, nas implementações que as 
permitem. Tente resolver este exercício sem auxílio do computador e explique os resultados. 
a. 
select sum (1) as x 
from aluno 
b. 
select sum (1) as x 
from aluno, inscricao, disciplina 
c. 
select sum (1) as x 
from ( aluno a inner join inscricao i on a.matricula = i.matricula ) 
 inner join disciplina d on i.coddisciplina = d.coddisciplina 
6.9 Considere as duas tabelas RESULTADO e JOGO, mostradas abaixo. A primeira contém os 
primeiros 500 resultados da Megasena, onde a cada concurso são sorteadas 6 dezenas, que 
podem variar entre 1 e 60; a segunda contém entre 6 e 10 palpites de dezenas. 
 Resultado 
Concurso Dezena 
1 4 
1 5 
1 30 
1 33 
1 41 
1 52 
... ... 
500 21 
500 29 
500 35 
500 36 
500 38 
500 54 
 Jogo 
Dezena 
12 
17 
26 
27 
52 
53 
 
28 Capítulo 6: Somando, contando e agrupando 
 
Escreva consultas SQL que produzam: 
a. o ranking numerado das dezenas, contendo o número de concursos em que cada uma das 
60 dezenas forma sorteadas; 
Ordem Dezena Número de concursos 
1 ... ... 
2 ... ... 
... ... ... 
60 ... ... 
 
 
b. para um jogo qualquer, quantos acertos teriam ocorrido para cada um dos concursos 
havidos. Mostre apenas os concursos onde houve pelo menos um acerto. 
 
Concurso Número de acertos 
... ... 
... ... 
 
 
c. repetir o item (b), porém incluindo os concursos para os quais houve zero acertos. 
 
6.10 Como calcular e média de um conjunto de valores sem o auxílio das funções de 
agregação? Considerando a tabela Páreo, do exemplo 6.6, escreva uma consulta que calcule o 
tempo médio dos cavalos sem utilizar funções de agregação. Note que a solução deverá ser 
específica para instância mostrada, não sendo genericamente aplicável se, por exemplo, o 
número de cavalos tiver aumentado ou diminuído.