Como agrupar resultados SQL por mês e ano?

Question

Gostaria de saber como posso fazer uma consulta ao banco de dados MySql e agrupar os registros por ano e mês.

No momento eu faço primeiro uma consulta para obter os registros de ano, depois para cada ano eu uso um for() para filtrar os resultados por mês dentro daquele ano. Por exemplo:

$ano = sql("SELECT DISTINCT YEAR(dataCadastro) as 'ano' FROM tabela");

foreach ($ano as &$row) {
    $ano = $row['ano'];

for ($i=1; $i<=12; $i++) {
        $item = sql("SELECT id, ... FROM tabela WHERE YEAR(dataCadastro) = '$ano' AND MONTH(dataCadastro) = '$i'");
        $row[$i] = $item;
    }
}

Pesquisando sobre o assunto vi que poderia usar GROUP BY, mas quando tento usar, a consulta retorna vazia, mesmo tendo certeza de que teria resultados. Por exemplo:

$grupo = sql("SELECT id, ... FROM tabela"); //Retorna os dados corretamente
$grupo = sql("SELECT id, ... FROM tabela GROUP BY YEAR(dataCadastro)"); //Retorna vazio

A data no banco de dados está como datetime, ex.: 2016-11-09 17:30:00

Existe algum modo de obter esse resultado utilizando o GROUP BY? Pois pelas pesquisas que fiz, observei que sim, mas quando tento executar, não consigo obter os resultados.

Lembrando que o primeiro modo que mostrei (com o uso de foreach) eu consigo obter os resultados como eu desejo, porém gostaria de simplificar o processo.

Alisson Custodio · Answer

A forma mais simples é concatenando o mês e ano:
CONCAT( MONTH(data_criacao), '/', YEAR(data_criacao) ) AS data

isso vai produzir algo como:
10/2021

Mauro Lacerda · Answer

SELECT extract(month FROM nome_campo) mes, extract(year FROM nome_campo) ano
FROM datas
GROUP BY extract(month FROM nome_campo), extract(year FROM nome_campo)

Bacco · Answer

Sobre o GROUP BY

Se quer usar agregação, é basicamente isso, mas não acredito que é o que você está buscando na realidade, se vai usar todas as linhas para relatório:

SELECT
  SUM( `valor` ) AS total
FROM
  datas
GROUP BY
  YEAR( `data` ), MONTH( `data` );

Funcionou localmente na versão 5.1.58, e na 5.6 no SQL Fiddle, segue link:

http://sqlfiddle.com/#!9/f03c6/1

Para versões de 5.7.5 em diante, observar a flag mencionada na resposta do @rray. Você pode executar um SELECT @@version; para saber qual está usando.

Sobre o problema aparente da pergunta

Se quiser todas as linhas, para agrupar só na saida, por exemplo, por cabeçalhos em PHP separando os blocos, use ORDER BY em vez de GROUP BY:

SELECT
  id, `data`
FROM
  datas
ORDER BY
  YEAR( `data` ), MONTH( `data` );

Aí basta um if no loop de exibição para mostrar o título somente quando muda o mês e ano. Que é o mesmo problema desta questão aqui, com código de exemplo:

Montar not&#237;cia agrupando resultados por classe de usu&#225;rio

(eu nem precisava mencionar isso de tão básico, mas se quiser imprimir os meses vazios, é mera questão de colocar um loop no lugar do if)

rray · Answer

O GROUP BY do MySQL não segue o padrão ansi ou seja na maioria banco de dados existe a obrigatóriedade de agrupar por todos campos que estão na lista de campos (após a palavra  SELECT). Essa mudança ocorreu a partir da versão 5.7.5 do MySQL.

Versãos anteriores a 5.7.5

SELECT id, nome, email FROM tabela GROUP BY id

A partir do 5.7.5 sua consulta deve ficar:

SELECT id, nome, email FROM tabela GROUP BY id, nome, email.

O nome de configuração é ONLY_FULL_GROUP_BY e pode desativada setando a variável sql_mode

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

MySQL - Documentação

Disable ONLY_FULL_GROUP_BY

Como agrupar resultados SQL por mês e ano?

4 Answers

Sobre o GROUP BY

Sobre o problema aparente da pergunta

Add your own answers!

Ask a Question