CODEWITHSUNDEEP
sqlsubqueryaggregate-functions
MarkusMulholland
MarkusMulholland

Reputation: 339

Using Min aggregate function on Count aggregate function

I used this query:

 select D.DeptID, D.Dept, count(E.EmployeeID) as TotalStaff
 from Employees as E
 right join Departments as D
 on D.DeptID = E.DeptID 
 group by D.DeptID, D.Dept;

To return this: 

DeptID_|_Dept___________|TotalStaff
40     | Marketing      | 2
50     | Accounting     | 3
60     | Manager        | 3
70     | GeneralStaff   | 1
80     | HumanResources | 1
90     | Production     | 0
100    | Sales          | 0

Now I want to list the department ID, department and the number of employees for the department that has the lowest number of employees so I tried this:

SELECT MIN(mycount) 
FROM 
(
 select D.DeptID, count(E.EmployeeID) as mycount
 from Employees as E
 right join Departments as D
 on D.DeptID = E.DeptID 
 group by D.DeptID
);

But I get an error that states: Incorrect syntax near ';'. All I want to do is return the department that has the lowest amount of employees. Please could anyone help me with this.

Upvotes: 5

Views: 112

Answers (4)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You have missed the subquery alias

So, your subquery has alias like that

SELECT MIN(mycount) 
FROM (select D.DeptID, count(E.EmployeeID) as mycount
      from Employees as E
      right join Departments as D on D.DeptID = E.DeptID 
      group by D.DeptID
     ) s; -- alias missed

Upvotes: 2

Vikram Jain
Vikram Jain

Reputation: 5588

please try with below query:

For minimum value:

 select min(A.mycount) as min_count
    from  (
         select D.DeptID, count(E.EmployeeID) as mycount
         from Departments as D
         left outer join Employees as E on D.DeptID = E.DeptID 
         group by D.DeptID
    ) as A

For maximum value:

select max(A.mycount) as max_count
from  (
     select D.DeptID, count(E.EmployeeID) as mycount
     from Departments as D
     left outer join Employees as E on D.DeptID = E.DeptID 
     group by D.DeptID
) as A

Upvotes: 2

George Menoutis
George Menoutis

Reputation: 7240

try adding 

as tablename

after your last ) and before the final ;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

The normal way to write this query is to use the ANSI standard rank() function:

select d.*
from (select D.DeptID, D.Dept, count(E.EmployeeID) as TotalStaff,
             rank() over (order by count(E.EmployeeID) asc) as seqnum
      from Departments d left join
           Employees E
           on D.DeptID = E.DeptID 
      group by D.DeptID, D.Dept
     ) d
where seqnum = 1;

Notice that I also switched the JOIN to a LEFT JOIN. LEFT JOIN is generally simpler to follow (at least for people who read languages left-to-right) because it says to keep all rows in the first table rather than the last table.

Upvotes: 1

Related Questions