0
SELECT  departemen_id, departemen_name, COUNT(*)
FROM    departement d
JOIN    employees e
ON      departemen_id = departement_id
GROUP BY departemen_id, departemen_name
HAVING  COUNT(*) = (
            SELECT  MAX(COUNT(*))
            FROM    employees
            GROUP BY departement_id
        );
Stefano Zanini
  • 5,876
  • 2
  • 13
  • 33
Doto Nendo
  • 3
  • 3

1 Answers1

0

The problem is that MAX(COUNT(*)) doesn't make any sense to the dbms. To follow that path you'd have to use another level of inner query.

HAVING  COUNT(*) = (
            SELECT  MAX(CNT) FROM (
                SELECT  departement_id, COUNT(*) as CNT
                FROM    employees
                GROUP BY departement_id
            )
        );

You can take advantages of the row number limiting capabilities of MySQL though, and your query will be simpler and better performing

SELECT  d.departement_id, d.departement_name, COUNT(*)
FROM    departement d
JOIN    employees e
ON      d.departement_id = e.departement_id
GROUP BY d.departement_id, d.departement_name
ORDER BY COUNT(*) DESC
LIMIT 1;
Stefano Zanini
  • 5,876
  • 2
  • 13
  • 33
  • SELECT departement_id, MIN(salary) FROM employees GROUP BY departement_id HAVING AVG(salary) = (SELECT MAX(AVG(salary)) FROM employees GROUP BY departement_id); whats wrong again bro – Doto Nendo Apr 21 '17 at 15:42
  • It's the exact same problem, `MAX(AVG(salary))` doesn't make any sense. You either use another subquert (select max(avg) from (select dept, avg(salary)... ) or use `order by avg(salary) desc limit 1`. – Stefano Zanini Apr 21 '17 at 15:51
  • tq so much bro (y) – Doto Nendo Apr 21 '17 at 17:25