-5
SELECT department_name, cou  FROM 
(
    SELECT department_name, count(student_id) AS cou
    FROM department d NATURAL JOIN student s
    GROUP BY department_name
)
GROUP BY department_name HAVING cou=max(cou);
APC
  • 144,005
  • 19
  • 170
  • 281
sheshin
  • 101
  • 1
  • 1
    What's the full error message you're getting, please? I don't know about you, but I'm not a walking encyclopaedia of Oracle error messages and their associated error codes! – Boneist Jun 13 '17 at 08:24
  • `cou=max(cou)` ? And also you are not using `cou` in group by but using it in select? – Utsav Jun 13 '17 at 08:29
  • Please read this other thread to understand the simplest way to do a [top-N query](https://stackoverflow.com/a/3574066/146325) – APC Jun 13 '17 at 09:46

2 Answers2

0

First off - avoid natural join. Its dangerous. Google for the reasons. The message you are getting is because you have a GROUP BY for the OUTER select but you have no aggregating function in the select clause.

It looks like you can probably just lose the outer select completely and put the HAVING clause on the inner select.

BriteSponge
  • 1,034
  • 9
  • 15
0
SELECT * FROM (
SELECT department_name, count(student_id) AS cou
  FROM department d 
  JOIN student s USING (student_id)
GROUP BY department_name
ORDER BY count(student_id) DESC )
WHERE rownum = 1
RLOG
  • 620
  • 6
  • 14