MySQL Funções de agregação: SOMA, CONTAR, AVG & MÁXIMO

O que são funções agregadas em MySQL?

An função agregada Lê várias linhas de uma única coluna e as consolida em um único valor. As funções de agregação servem para:

• Realizar cálculos em várias linhas
• De uma única coluna de uma tabela
• E retornando um único valor.

A norma ISO define cinco (5) funções agregadas, a saber:

1. CONTAGEM
2. SOMA
3. AVG
4. MIN
5. MAX

Uma única regra se aplica a todos os cinco: As funções de agregação ignoram valores NULL.. COUNT(*) é a única exceção, e veremos o porquê abaixo.

Por que usar funções agregadas

Os diferentes níveis hierárquicos de uma organização têm diferentes necessidades de informação. Os gestores de topo geralmente estão interessados ​​em números absolutos, e não em detalhes individuais.

As funções agregadas nos permitem produzir facilmente dados resumidos de nosso banco de dados.

Por exemplo, a partir do nosso banco de dados Myflix, a gerência pode exigir os seguintes relatórios:

• Filmes menos alugados.
• Filmes mais alugados.
• Número médio de vezes que cada filme é alugado por mês.

Todos os relatórios acima são provenientes de funções de agregação. Vamos analisar cada uma delas em detalhes.

Função COUNT

A função COUNT retorna o número total de valores no campo especificado, tanto para tipos de dados numéricos quanto não numéricos. Como toda função de agregação, COUNT(coluna) exclui valores NULL.

COUNT(*) é uma forma especial que retorna a contagem de todas as linhas em uma tabela. Ela também conta NULOS e duplicados, porque conta linhas em vez de valores.

A tabela de aluguéis de filmes contém os seguintes dados:

Suponhamos que queiramos obter o número de vezes que o filme com o ID 2 foi alugado.

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

Executando isso em MySQL Workbench A consulta ao myflixdb retorna 3, porque três linhas contêm o movie_id 2.

Palavra-chave DISTINTA

A resposta para "contar" é "quantos?". A próxima pergunta geralmente é "quantos?". diferente “uns”, e é para isso que serve o DISTINCT.

A palavra-chave DISTINCT omite duplicados dos nossos resultados por grupo.ping Valores idênticos juntos, exatamente como sugere a ilustração acima.

Primeiro, vamos executar uma consulta simples.

SELECT `movie_id` FROM `movierentals`;

Agora, a mesma consulta com a palavra-chave DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT omite os registros duplicados:

COUNT vs COUNT(*) vs COUNT(DISTINCT): Qual você deve usar?

DISTINCT também pode ser colocado dentro uma função agregada, e é aqui que a maioria dos iniciantes se perde. track das quais linhas são efetivamente contadas. Os quatro formulários abaixo são executados na mesma tabela de aluguel de filmes de cinco linhas mostrada anteriormente, mas não retornam o mesmo número. A diferença reside em duas questões: o formulário conta linhas ou valores e mantém duplicados?

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 Dica: Use COUNT(*) para contar as linhas, COUNT(coluna) quando um valor NULL deve significar "não se aplica" e COUNT(DISTINCT coluna) para valores únicos. O oposto de DISTINCT é ALL — o padrão, e portanto raramente escrito por extenso.

Função MIN

A função MÍN. retorna o menor valor no campo da tabela especificado.

Suponhamos que queiramos saber o ano em que o filme mais antigo da nossa biblioteca foi lançado. MySQLA função MIN de 's nos fornece isso.

SELECT MIN(`year_released`) FROM `movies`;

Resultado:

Função MAX

Tal como o nome sugere, a função MAX é o oposto da função MIN. Isto retorna o maior valor do campo da tabela especificado.

Suponha que queiramos saber o ano de lançamento do filme mais recente em nosso banco de dados. O exemplo a seguir retorna essa informação.

SELECT MAX(`year_released`) FROM `movies`;

Resultado:

Função SUM

MIN e MAX selecionam um valor existente de uma coluna. SUM e AVG Calcule um novo número a partir de toda a coluna.

Suponha que desejemos o valor total dos pagamentos efetuados até o momento. MySQL SOMA função Retorna a soma de todos os valores na coluna especificada.. SUM funciona apenas em campos numéricos e Valores NULL são excluídos do resultado..

A tabela a seguir mostra os dados da tabela de pagamentos.

A consulta mostrada abaixo obtém todos os pagamentos efetuados e os soma em um único resultado: 2500 + 2000 + 6000 = 10500.

SELECT SUM(`amount_paid`) FROM `payments`;

Resultado:

AVG função

O MySQL AVG função retorna a média dos valores em uma coluna especificada. Assim como a função SUM, ela funciona apenas em tipos de dados numéricos.

Suponha que queiramos encontrar o valor médio pago. Podemos usar a seguinte consulta, que divide o total de 10500 pelas três linhas de pagamento que não contêm valores nulos.

SELECT AVG(`amount_paid`) FROM `payments`;

Resultado:

⚠️ Aviso: AVG A divisão é feita pelo número de linhas não nulas, e não pela quantidade de linhas da tabela. Um valor nulo é ignorado em vez de ser contado como zero, o que silenciosamente aumenta a média. Use AVG(IFNULL(`amount_paid`, 0)) quando um valor ausente significa zero.

Exemplo prático: Combinando funções de agregação com GROUP BY

Cada função acima retornou um valor para a tabela inteira. Adicionando um GROUP BY A cláusula retorna um número. por grupo Em vez disso — e é assim que se constroem relatórios verdadeiros.

O exemplo a seguir agrupa os membros por nome e, em seguida, contabiliza o número total de pagamentos, o valor médio do pagamento e o total geral dos valores pagos a cada membro.

SELECT m.`full_names`,
       COUNT(p.`payment_id`) AS `paymentscount`,
       AVG(p.`amount_paid`) AS `averagepaymentamount`,
       SUM(p.`amount_paid`) AS `totalpayments`
FROM members m, payments p
WHERE m.`membership_number` = p.`membership_number`
GROUP BY m.`full_names`;

Executando o exemplo acima em MySQL O Workbench nos fornece os seguintes resultados.

A consulta une as duas tabelas na cláusula WHERE — o estilo antigo de junção por vírgula. O código moderno escreve a mesma lógica de forma explícita. JUNÇÃO INTERNA… LIGADOObserve também que todas as colunas não agregadas na lista SELECT devem aparecer em GROUP BY, ou MySQL A versão 5.7 e posteriores rejeitam a consulta em ONLY_FULL_GROUP_BY. Consulte o oficial MySQL referência de função agregada.

Perguntas Frequentes