max(count(*))....???

i has a problem about this query...

SELECT d.deptno, d.dname, COUNT(*)
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY d.deptno, d.dname
HAVING COUNT(*) = (SELECT max(count(*))
FROM emp
group by deptno);


error : Invalid use of group function

how do i can get the max value of the count??..

please help...

 

Hi Namuk

This is one solution

SELECT d.deptno, d.dname, COUNT(*)
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY d.deptno, d.dname
HAVING COUNT(*) = (SELECT max(c)
FROM ( SELECT deptno, COUNT(*) c
FROM emp
group by deptno));

 

What do you think about this?

SELECT d.deptno, d.dname, COUNT(*) AS cnt
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY d.deptno, d.dname
ORDER BY cnt DESC 
LIMIT 1

Code (markup):

 

query is working

 

Which one worked?

 

The two queries will give different results if two or more departments have the equally the most employees.
It all depands on the result you are after.