Ask the Expert

Counts from two different tables in one query

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."


    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: