Problem with group by

Asked

Viewed 1,104 times

5

I have the following query:

SELECT funcionario.*, emprego.data_admissao, emprego.data_saida, emprego.data_dispensa 
FROM funcionario,emprego,empresa 
WHERE empresa.id = emprego.id_empresa
  AND funcionario.id = emprego.id_funcionario 
  AND emprego.data_admissao IS NOT NULL 
  AND empresa.id = 2 GROUP BY funcionario.id

She is presenting the following error:

#1055 - Expression #28 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'carteira.emprego.data_admissao' which is not functionally dependent on Columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Then I add the group by of the 'data_admissao' and the other fields for not error:

SELECT funcionario.*, emprego.data_admissao, emprego.data_saida, emprego.data_dispensa 
FROM funcionario,emprego,empresa 
WHERE empresa.id = emprego.id_empresa
  AND funcionario.id = emprego.id_funcionario 
  AND emprego.data_admissao IS NOT NULL 
  AND empresa.id = 2 
GROUP BY funcionario.id, emprego.data_admissao, emprego.data_saida, emprego.data_dispensa

So it works, however I wanted the items to be grouped by the 'functio.id' and when I run this query the records repeat themselves.

I would like the records to be grouped by the 'funcio.id' only, when running this second query it works, but the 'funcio.id' repeats itself.

Update

I removed "*" from the query just to make it easy to show the result here.

I executed:

SELECT funcionario.id, emprego.data_admissao, emprego.data_saida, emprego.data_dispensa FROM funcionario,emprego,empresa WHERE empresa.id = emprego.id_empresa AND funcionario.id = emprego.id_funcionario AND emprego.data_admissao IS NOT NULL AND empresa.id = 2 GROUP BY funcionario.id, emprego.data_admissao, emprego.data_saida, emprego.data_dispensa

Upshot:

inserir a descrição da imagem aqui

mysql group-by

  • And if you add the records of each employee in an array?

  • Using INNER JOIN also give that mistake?

  • You’re looking for work. * and not working.ID, another thing you should look at is whether there are saved more than one date of admission/exit/discharge for the same employee.

  • I updated the question, look at the result.

  • @R.Santos I do not understand, you mean in the code receive everything and separate?

  • @Robertodecampos I haven’t tried with Ner Join.

  • 1

    Tries: SELECT funcionario.id, max(emprego.data_admissao), max(emprego.data_saida), max(emprego.data_dispensa) 
FROM funcionario,emprego,empresa 
WHERE empresa.id = emprego.id_empresa
 AND funcionario.id = emprego.id_funcionario 
 AND emprego.data_admissao IS NOT NULL 
 AND empresa.id = 2 
GROUP BY funcionario.id

  • @Laérciolopes worked expensive, put and explains the query I will mark as the answer of the question (y)

Show 3 more comments

2 answers

6


To use the group by it is necessary to use an aggregation function such as count, max, min, sum...

If you want to obtain the last date of admission, exit and dispensation grouped by the ID, you can obtain this data with the following query:

SELECT funcionario.id, max(emprego.data_admissao), max(emprego.data_saida), max(emprego.data_dispensa)
FROM funcionario, emprego, empresa
WHERE empresa.id = emprego.id_empresa
AND funcionario.id = emprego.id_funcionario
AND emprego.data_admissao IS NOT NULL
AND empresa.id = 2
GROUP BY funcionario.id

  • See if the data you need are the same returned.

  • This straight, thank you very much!

  • All right then! (Y).

0

My friend, this error occurs because of the Mysql variable "sql mode", because it has set values in its content, it may be that some of these values are creating a kind of lock for your queries. to resolve this error you can use before starting your SQL query the following code below:

SET sql_mode='';
SELECT sua querie...

This will disable sql mode in every query you perform. I faced this problem and solved it this way.

Browser other questions tagged mysql group-by

You are not signed in. Login or sign up in order to post.