How to group SQL results by month and year?

Navigation
4

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

Searching 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 as datetime , eg 2016-11-09 17:30:00

Is there any way to get this result using GROUP BY ? Because of the searches I've done, I've noticed that, but when I try to run, I can not get the results.

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

    
asked by anonymous 09.11.2016 / 20:36

3 answers

6

The GROUP BY of MySQL does not follow the ansi standard or in most database there is a requirement to group by all fields that are in the field list (after the word SELECT ). This change occurred from version 5.7.5 of MySQL.

Versions prior to 5.7.5 

SELECT id, nome, email FROM tabela GROUP BY id

From 5.7.5 your query should be: 

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

    
09.11.2016 / 21:42
6

About GROUP BY

If you want to use aggregation, it's basically this, but I do not believe that's what you're really looking for, if you'll use all the rows to report: 

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 the link:

  

link

For versions of 5.7.5 onwards, note the flag mentioned in the @rray response. You can run SELECT @@version; to see which one you are using.


About the apparent problem of the question

If you want all the 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 a if in the display loop is enough to show the title only when the month and year changes. Which is the same problem of this issue here, with sample code:

  

Assemble news by grouping results by user class

    
09.11.2016 / 22:02
0
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)
    
08.10.2018 / 14:51
Clear the browser cache after uploading version Set the value 0 when the number is negative