Problem with group by

Question

Problem with group by

Navigation

#1 by (6 votes)

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

It is displaying the following error:

# 1055 - Expression # 28 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'portfolio.employ.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 'data_admissao' and the other field to not the 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, but I wanted the items to be grouped by 'official.id' and when I run this query the records are repeated.

I would like the records to focus grouped by 'official.id' only, when executing this second query it works, but 'official.id' is repeated.

Update

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

I performed:

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

Result:

mysql group-by

asked by anonymous 20.11.2017 / 17:04

1 answer

Creating tables with database scripts What are the terms RISC and CISC in relation to software development?

score 6 · Accepted Answer

To use group by you need to use an aggregate function such as count , max , min , sum ...

If you want to get the last date of admission, exit and waiver 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