Error when using Group by in a VIEW in Mysql

6

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

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 has been 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 input jose da silva N

I then made the following query:

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

This 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 error about? And what would be the best way to treat this query ?

    
asked by anonymous 05.12.2015 / 23:18

1 answer

0

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

Example:

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

This would cause students' scores to be summed and grouped according to the name of each student.

In your case, you are not doing any aggregation with the rest of your columns. .. protocol, der, dhab, desandamento, name . .. or you do some aggregation ( sum, min, max , ...) or put all the columns in group by .

    
06.12.2015 / 18:24