Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I need to display the department name and the number of employees of the department with more employees. However I am receiving the following error (#1111 - Invalid use of group function). Here's the query:

select e.dept_no, dnombre, count(*) from emple e, depart d
where e.dept_no=d.dept_no
group by e.dept_no, dnombre
having count(*)=(select max(count(*)) from emple group by dept_no);
I need help with this query in MySQL.
Instead of Nesting aggregates use Limit to get the max count. Try this..
select e.dept_no, dnombre, count(*) from emple e, depart d
where e.dept_no=d.dept_no
group by e.dept_no, dnombre
having count(*)=(select count(*) from emple group by dept_no order by count(*) Desc limit 1);
Used a left join in case all departments had no employees which is not handled in this set due to an inner join.
used a group by on d.dept_no since e.dept number could be null.
ordered by count of d.depts dec
used a limit operator to only return "ONE" max record.  Note, this approach will not show ties..
SELECT count(d.dept), d.dnombre, d.dept_no
FROM Emple e
LEFT JOIN depart d
 on E.dept_no=d.dept_no
GROUP BY d.dept_no, d.dnombre
ORDER BY count(d.dept) desc
LIMIT 1;
if you want to use the MAX function you can also do it like this:
SELECT e.dept_no, dnombre, COUNT(*)
FROM emple e, depart d
WHERE e.dept_no=d.dept_no
GROUP BY e.dept_no, dnombre
HAVING COUNT(*) = (SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM emple GROUP BY dept_no) T);
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.