MySQL GRUPO POR

Resumo: neste tutorial, você aprenderá como usar o MySQL GROUP BYpara agrupar linhas em grupos com base nos valores de colunas ou expressões.

Introdução à cláusula MySQL GROUP BY

A GROUP BYcláusula agrupa um conjunto de linhas em um conjunto de linhas de resumo com base em valores de colunas ou expressões. Ele retorna uma linha para cada grupo e reduz o número de linhas no conjunto de resultados.

A GROUP BYcláusula é uma parte opcional da SELECT declaração. O seguinte ilustra a sintaxe da GROUP BYcláusula:

SELECT 
    c1, c2,..., cn, aggregate_function(ci)
FROM
    table_name
WHERE
    conditions
GROUP BY c1 , c2,...,cn;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe, você coloca a GROUP BYcláusula após as cláusulas FROMe WHERE. Após as GROUP BYpalavras-chave, você lista as colunas ou expressões que deseja agrupar, separadas por vírgulas.

O MySQL avalia a GROUP BYcláusula após as cláusulas FROMe , WHEREmas antes das cláusulas HAVING, SELECT, e :DISTINCTORDER BYLIMIT

Na prática, você costuma usar a GROUP BYcláusula com funções agregadas como SUM, AVG, MAX, MINe COUNT. A função agregada que aparece na SELECTcláusula fornece as informações de cada grupo.

Exemplos de GROUP BY do MySQL

Vejamos alguns exemplos de uso da GROUP BYcláusula.

1) Exemplo básico do MySQL GROUP BY

Usaremos a orderstabela no banco de dados de exemplo :

Se quiser agrupar os status do pedido, você pode usar a GROUP BYcláusula com a statuscoluna na seguinte consulta:

SELECT 
  status 
FROM 
  orders 
GROUP BY 
  status;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

Saída:

+------------+
| status     |
+------------+
| Shipped    |
| Resolved   |
| Cancelled  |
| On Hold    |
| Disputed   |
| In Process |
+------------+
6 rows in set (0.02 sec)Linguagem de código:  JavaScript  ( javascript )

A saída mostra que a GROUP BYcláusula retorna ocorrências únicas dos valores nas statuscolunas.

Funciona como o DISTINCT operador, conforme demonstrado na consulta a seguir:

SELECT 
  DISTINCT status 
FROM 
  orders;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

2) Usando MySQL GROUP BY com funções agregadas

Na prática, você costuma usar a GROUP BYcláusula com uma função agregada para agrupar linhas em conjuntos e retornar um único valor para cada grupo.

Uma função agregada realiza o cálculo de um conjunto de linhas e retorna um único valor.

Por exemplo, para obter o número de pedidos em cada status, você pode usar a COUNTfunção com a GROUP BYcláusula a seguir:

SELECT 
  status, 
  COUNT(*) 
FROM 
  orders 
GROUP BY 
  status;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

+------------+----------+
| status     | COUNT(*) |
+------------+----------+
| Shipped    |      303 |
| Resolved   |        4 |
| Cancelled  |        6 |
| On Hold    |        4 |
| Disputed   |        3 |
| In Process |        6 |
+------------+----------+
6 rows in set (0.01 sec)Linguagem de código:  JavaScript  ( javascript )

Consulte o seguinte orderse   orderdetailsa tabela do banco de dados de amostra:

Para obter o valor total de todos os pedidos por status, você junta a orderstabela com a orderdetailstabela e utiliza a SUMfunção para calcular o valor total:

SELECT 
  status, 
  SUM(quantityOrdered * priceEach) AS amount 
FROM 
  orders 
  INNER JOIN orderdetails USING (orderNumber) 
GROUP BY 
  status;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

+------------+------------+
| status     | amount     |
+------------+------------+
| Shipped    | 8865094.64 |
| Resolved   |  134235.88 |
| Cancelled  |  238854.18 |
| On Hold    |  169575.61 |
| Disputed   |   61158.78 |
| In Process |  135271.52 |
+------------+------------+
6 rows in set (0.01 sec)Linguagem de código:  JavaScript  ( javascript )

Da mesma forma, a consulta a seguir retorna os números dos pedidos e o valor total de cada pedido.

SELECT 
  orderNumber, 
  SUM(quantityOrdered * priceEach) AS total 
FROM 
  orderdetails 
GROUP BY 
  orderNumber;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

3) MySQL GROUP BY com exemplo de expressão

Além das colunas, você pode agrupar linhas por expressões. A consulta a seguir calcula o total de vendas de cada ano:

SELECT 
  YEAR(orderDate) AS year, 
  SUM(quantityOrdered * priceEach) AS total 
FROM 
  orders 
  INNER JOIN orderdetails USING (orderNumber) 
WHERE 
  status = 'Shipped' 
GROUP BY 
  YEAR(orderDate);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

+------+------------+
| year | total      |
+------+------------+
| 2003 | 3223095.80 |
| 2004 | 4300602.99 |
| 2005 | 1341395.85 |
+------+------------+
3 rows in set (0.02 sec)Linguagem de código:  JavaScript  ( javascript )

Neste exemplo, utilizamos a YEARfunção para extrair dados do ano da data do pedido ( orderDate) e incluímos apenas os pedidos com shippedstatus no total de vendas.

Observe que a expressão na SELECTcláusula deve corresponder àquela na GROUP BYcláusula.

4) Usando MySQL GROUP BY com exemplo de cláusula HAVING

Para filtrar os grupos retornados pela GROUP BYcláusula, você usa uma  HAVING cláusula.

A consulta a seguir usa a HAVINGcláusula para selecionar o total de vendas dos anos após 2003.

SELECT 
  YEAR(orderDate) AS year, 
  SUM(quantityOrdered * priceEach) AS total 
FROM 
  orders 
  INNER JOIN orderdetails USING (orderNumber) 
WHERE 
  status = 'Shipped' 
GROUP BY 
  year 
HAVING 
  year > 2003;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

5) Agrupamento por múltiplas colunas

A consulta a seguir retorna o ano, o status do pedido e o pedido total para cada combinação de ano e status do pedido agrupando as linhas em grupos:

SELECT 
  YEAR(orderDate) AS year, 
  status, 
  SUM(quantityOrdered * priceEach) AS total 
FROM 
  orders 
  INNER JOIN orderdetails USING (orderNumber) 
GROUP BY 
  year, 
  status 
ORDER BY 
  year;Linguagem de código:  PHP  ( php )

Saída:

+------+------------+------------+
| year | status     | total      |
+------+------------+------------+
| 2003 | Cancelled  |   67130.69 |
| 2003 | Resolved   |   27121.90 |
| 2003 | Shipped    | 3223095.80 |
| 2004 | Cancelled  |  171723.49 |
| 2004 | On Hold    |   23014.17 |
| 2004 | Resolved   |   20564.86 |
| 2004 | Shipped    | 4300602.99 |
| 2005 | Disputed   |   61158.78 |
| 2005 | In Process |  135271.52 |
| 2005 | On Hold    |  146561.44 |
| 2005 | Resolved   |   86549.12 |
| 2005 | Shipped    | 1341395.85 |
+------+------------+------------+
12 rows in set (0.01 sec)Linguagem de código:  JavaScript  ( javascript )

A cláusula GROUP BY: MySQL vs. padrão SQL

O padrão SQL não permite que você use um alias na GROUP BYcláusula, enquanto o MySQL suporta isso.

Por exemplo, a consulta a seguir extrai o ano da data do pedido. Ele primeiro usa o yearcomo um alias da expressão YEAR(orderDate)e depois usa o yearalias na GROUP BYcláusula.

A consulta a seguir não é válida no padrão SQL:

SELECT 
  YEAR(orderDate) AS year, 
  COUNT(orderNumber) 
FROM 
  orders 
GROUP BY 
  year;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

+------+--------------------+
| year | COUNT(orderNumber) |
+------+--------------------+
| 2003 |                111 |
| 2004 |                151 |
| 2005 |                 64 |
+------+--------------------+
3 rows in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

A cláusula GROUP BY vs. cláusula DISTINCT

Se você usar a GROUP BYcláusula na SELECTinstrução sem usar funções agregadas , a GROUP BYcláusula se comportará como a DISTINCT cláusula.

A instrução a seguir usa a GROUP BYcláusula para selecionar os estados exclusivos dos clientes na customerstabela.

SELECT 
  state 
FROM 
  customers 
GROUP BY 
  state;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

+---------------+
| state         |
+---------------+
| NULL          |
| NV            |
| Victoria      |
| CA            |
| NY            |
| PA            |
| CT            |
...Linguagem de código:  PHP  ( php )

Você pode obter um resultado semelhante usando a DISTINCTcláusula:

SELECT 
  DISTINCT state 
FROM 
  customers;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

+---------------+
| state         |
+---------------+
| NULL          |
| NV            |
| Victoria      |
| CA            |
| NY            |
| PA            |
| CT            |
...

Linguagem de código:  PHP  ( php )

Observe que o MySQL 8.0 ou posterior removeu a classificação implícita da GROUP BYcláusula. Portanto, se você estiver usando versões anteriores, descobrirá que o conjunto de resultados com a GROUP BYcláusula está classificado.

Resumo

• Use a GROUP BYcláusula para agrupar linhas em subgrupos.