How to group SQL results by month and year?

Question

I would like to know how I can query the Mysql database and group the records by year and month.

---

At the moment I first make a query to get the year records, then for each year I use a for() to filter the results per month within that year. For example:

$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;
    }
}

Researching on the subject I saw that I could use GROUP BY, but when I try to use it, the query returns empty, even though I’m sure it would have results. For example:

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

The date in the database is like datetime, ex.: 2016-11-09 17:30:00

Is there any way to get this result using the GROUP BY? Because from the research I’ve done, I’ve observed that yes, but when I try to execute, I can’t get the results.

Remembering that the first way I showed (with the use of foreach) I can get the results as I wish, but I would like to simplify the process.

Answer 1

The GROUP BY Mysql does not follow the ansi pattern, which means that in most databases there is a requirement to group all fields that are in the field list (after the word SELECT). This change occurred as of Mysql version 5.7.5.

Versions prior to 5.7.5

SELECT id, nome, email FROM tabela GROUP BY id

From 5.7.5 your consultation should stay:

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

The configuration name is ONLY_FULL_GROUP_BY and can be disabled by setting the variable sql_mode

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

Mysql - Documentation

Disable ONLY_FULL_GROUP_BY

Answer 2

About GROUP BY

If you want to use aggregation, that’s basically it, but I don’t believe that’s what you’re actually looking for, if you’re going to use all the lines for reporting:

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

It worked locally in version 5.1.58, and in 5.6 in SQL Fiddle, follow link:

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

For versions 5.7.5 onwards, note the flag mentioned in the @rray response. You can run a SELECT @@version; to know which one you’re using.

About the apparent problem of the question

If you want all rows, to group only in the output, for example by PHP headers separating the blocks, use ORDER BY instead of GROUP BY:

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

Then just one if in the display loop to show the title only when changing the month and year. That’s the same issue here, with sample code:

Assemble news by grouping results by user class

_{(I didn’t even need to mention it so basic, but if you want to print out the empty months, it’s just a matter of putting a loop in place of the if)}

Answer 3

The simplest way is to concatenate the month and year:

CONCAT( MONTH(data_criacao), '/', YEAR(data_criacao) ) AS data

this will produce something like:

10/2021

Answer 4

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)