Error using Group by in a Mysql VIEW

Question

I created a VIEW to return in a single query the main data I need.

CREATE 
ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY DEFINER

VIEW v_historicoProcesso AS
SELECT p.protocolo AS protocolo
      ,p.der AS der
      ,p.data_habilitacao AS dhab
      ,a.descricao AS desandamento u.nome AS nome
      ,ap.bol_ativo AS ativo


  FROM (((processo p JOIN andamento_processo ap
        ON((p.id = ap.id_processo))) JOIN usuario u
        ON((u.id = p.id_usuario))) JOIN andamento a
        ON((ap.id_andamento = a.id)))
 ORDER BY ap.data DESC

So far the return was satisfactory. Having the result something like:

123 2014-04-01 2014-02-02 agreed simao da silva S

123 2014-04-01 2014-02-02 agreed simao da silva N

123 2014-04-01 2013-02-02 agreed simao da silva N

456 2014-04-01 2014-02-02 without agreement Jose da silva N

456 2014-04-01 2014-02-02 exigencia Jose da silva S

456 2014-04-01 2014-02-02 entry Jose da silva N

I then carried out the following consultation:

SELECT protocolo
      ,der
      ,dhab
      ,desandamento
      ,nome
  FROM v_historicoprocesso
 WHERE ativo = 'S'
 GROUP BY idprocesso
 ORDER BY der       DESC
         ,protocolo ASC;

The result would be only the’S' assets of each idProcess

123 2014-04-01 2014-02-02 agreed simao da silva S

456 2014-04-01 2014-02-02 exigencia Jose da silva S

Such query returned the following error:

Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'v_historicoProcesso.desAndamento' which is not functionally dependent on Columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

What is this mistake about? And what would be the best way to treat this query?

Answer 1

This error occurs because the group by expects you to be grouping your data. For example, if you wanted to group all of a student’s grades during the school year, you could use the group by for that reason.

Example:

Select nome, sum (nota) from aluno
Group by nome;

This would cause students' grades to be summed and grouped according to each student’s name.

In your case, you are not aggregating with the rest of your columns. .. procedure, procedure, procedure, procedure, procedure. .. Or you make some aggregation (sum, min, max,...) or puts all columns in the group by.