Click here to Skip to main content
14,605,912 members
Rate this:
Please Sign up or sign in to vote.
See more:
want to display that which department have least number of employee ? and department with 2nd least number?

there are three tables
1)departments
*dept_no
*dept_name
2)dept_emp
*emp_no
*dept_no
3)employees
*emp_no
*emp_name

how to join these tables and find which department have least number of employee ? and department with 2nd least number?

What I have tried:

i had tried the following code

SELECT d.`dept_no`,
       d.`dept_name`,
  count  (dt.emp_no) as "Num of employees"
FROM departments d
  join dept_emp dt
  on d.dept_no=dt.dept_no
  having count(dt.emp_no)=
    (select min(count(de.emp_no))
    from departments d
    join dept_emp dt
    on d.dept_no=dt.dept_no
    group by d.dept_name)
group by d.dept_name;


but its showing error something like
"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'group by d.dept_name' at line 13"
Posted
Updated 31-Dec-19 0:37am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Well ... "de" doesn't exist:
(select min(count(de.emp_no))

And GROUP BY needs to precede HAVING:
having count(dt.emp_no)=
...
group by d.dept_name;

But why so complicated? A simple JOIN will do it:
SELECT TOP 2 d.DeptNo, d.DeptName, de.EmpPerDept
FROM Departments d
JOIN (
SELECT DeptNo, COUNT(EmpNo) AS EmpPerDept
FROM DeptEmp 
GROUP BY DeptNo) de
ON de.DeptNo = d.DeptNo
ORDER BY EmpPerDept ASC
   
v2
Comments
Oviya Sivakumar 2-Jan-20 0:44am
   
it works thank you
OriginalGriff 2-Jan-20 1:59am
   
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100