CODEWITHSUNDEEP
sqloracle-database
diksha 14UPH073
diksha 14UPH073

Reputation: 3

min(count()) function not working

I want to display the name of the departments that has the least student count. This is my query:

select department_name, count(student_id)
from   department d, student s
where  d.department_id = s.department_id
group by department_name;

This query works fine and shows student count, but when I add the min function as min(count(student_id)), it fails with:

ERROR at line 1: ORA-00937: not a single-group group function

I don't know what I am doing wrong. Any help/suggestion?

Upvotes: 0

Views: 233

Answers (5)

Caius Jard
Caius Jard

Reputation: 74605

As others have noted, when you wrapped the count in MIN that would have been getting the min PER DEPARTMENT. Because there is already only one row per department, this is a non-operation

What you say you want is a min over ALL DEPARTMENTS. Something like this:

Select department_name from(
 select department_name, count(student_id), rank() over(order by count(student_id)) as x
 from   department d, student s
 where  d.department_id = s.department_id
 group by department_name
)
Where x = 1

How it works? The rank() function assigns a position to rows in order, like winning a race. In this case the order is ascending count() of students, hence the minimum count is in position 1. What is special with rank is that rows with equal counts have an equal rank. We thus know that all rows that are ranked 1 are all equally the minimum count. To show only rows that are =1 we have to use a subquery, alas (I know you didn't want to see it) because oracle doesn't let us say something like where rank() over(...) =1. The subquery isn't really a performance penalty though, because the ranking is prepared at the same time as the rest of the data is single scanned, and then this passes through the filter of the outer where clause. This is different to the other posted answer that uses HAVING.. in that case the query is run, all the counts are found, the min of them all is found, then the query is run again, the counts are found again, and then the HAVING filters them down to the min that was found before

Upvotes: 2

Temuulen Altankhuyag
Temuulen Altankhuyag

Reputation: 1

It is an easy way. Goodluck

SELECT department_name, MIN(A)
FROM (select department_name, count(student_id) as A
from   department d, student s
where  d.department_id = s.department_id
group by department_name)

Upvotes: 0

William Robertson
William Robertson

Reputation: 16001

Here's another way:

select department_name, students
from   ( select department_name, count(*) as students
              , rank() over (order by count(*)) as seq
         from   department join student using(department_id)
         group by department_name )
where seq = 1;

Upvotes: 0

Karthiknarayanan
Karthiknarayanan

Reputation: 24

If I understand correctly, you need to find the department that has least number of students. Below query can give you the answer.

select department_name
from   department d, student s
where  d.department_id = s.department_id
group by department_name
having count(student_id) = (select min(a.cnt_std_id) from (select 
department_name, count(student_id) cnt_std_id
from   department d, student s
where  d.department_id = s.department_id
group by department_name)a);

Upvotes: 0

Temuulen Altankhuyag
Temuulen Altankhuyag

Reputation: 1

In this case, MIN() function works before GROUP BY function. So error came. You can try this.

SELECT TOP 1 department_name,count(student_id) as A
FROM department d,student s 
WHERE d.department_id=s.department_id 
GROUP BY department_name
ORDER BY A DESC

Upvotes: 0

Related Questions