How to perform this query correctly ? [Mysql]

Asked

Viewed 39 times

-1

In front of this database: Employee(employee, employee name, street, city) Works(employee, code company, salary) Company(code company, company name, city) Manager(employee, matricula_employed_manager)

I need to make this query: "Find the company that has the most employees."

My query:

SELECT c.nome_companhia, COUNT(t.matricula_empregado) AS Qtde 
FROM companhia AS c, trabalha AS t, empregado AS e
WHERE t.codigo_companhia = c.codigo_companhia 
AND t.matricula_empregado = e.matricula_empregado 
GROUP BY c.nome_companhia HAVING Qtde = (SELECT MAX(Qtde));

Error displayed : Error Code: 1247. Reference 'Qtde' not supported (Reference to group Function)

mysql sql query mysql-workbench

  • Important you [Dit] your question and explain objectively and punctually the difficulty found, accompanied by a [mcve] of the problem and attempt to solve. To better enjoy the site, understand and avoid closures and negativities worth reading the Stack Overflow Survival Guide in English.

1 answer

0


A way would be:

SELECT Companhia.codigo_companhia, Companhia.nome_companhia, Companhia.cidade, COUNT(Trabalha.matricula_empregado) AS Qtde
FROM Companhia INNER JOIN Trabalha ON (Companhia.codigo_companhia = Trabalha.codigo_companhia)
GROUP BY Companhia.codigo_companhia, Companhia.nome_companhia, Companhia.cidade
ORDER BY Companhia.codigo_companhia DESC
LIMIT 1

But how is it possible to have more than one company with the same greater number of employees:

SELECT Companhia.*
FROM Companhia
WHERE Companhia.codigo_companhia IN 
        (SELECT codigo_companhia, MAX(cont) AS Qtde 
        FROM    (SELECT Trabalha.codigo_companhia, COUNT(*) AS cont 
                FROM Trabalha 
                GROUP BY Trabalha.codigo_companhia) foo
        )

Browser other questions tagged mysql sql query mysql-workbench

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