Q

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


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close