Based on these entities:
EMPLOYEE FNAME LNAME SSN PK SUPERSSN FK to EMPLOYEE.SSN DNO FK to DEPARTMENT.DNUMBER DEPARTMENT DNAME DNUMBER PK MGRSSN FK to EMPLOYEE.SSN MGRSTARTDATE DEPT_LOCATIONS DNUMBER PK, FK to DEPARTMENT.DNUMBER DLOCATION PK
How would I write this query in SQL:
"For each department with more than one location, retrieve the department's name, department number, and the number of employees who work for the department."
The first count we want is for departments with more than one location. We don't actually want the count itself, we just need to use it in the HAVING clause when we GROUP BY department:
select dnumber from dept_locations group by dnumber having count(*) > 1
The second count is for each department, where we need the count of its employees. For this, we need to join the department and employee tables, and use GROUP BY again, but this time, it's the employee rows that are being counted:
select dname , dnumber , count(employees.dno) from department left outer join employees on department.dnumber = employees.dno group by dname , dnumber
Note that a LEFT OUTER JOIN is used, so that we get a count for all departments, including any with no employees. If a department has no employees, count(employees.dno) will be 0, while count(*) would be 1, because even if a department has no employees, there's still a row for it in the result set (in other words, each group in an outer join has at least one row).
Finally, we incorporate the first query as a subquery in the second, so that it qualifies which departments we're interested in:
select dname , dnumber , count(employees.dno) from department left outer join employees on department.dnumber = employees.dno where dnumber in ( select dnumber from dept_locations group by dnumber having count(*) > 1 ) group by dname , dnumber
Since the subquery is not correlated, the database can execute it ahead of the main query, and simply "feed" the main query a list of department numbers in the WHERE clause.
This was first published in May 2003