当前位置: 动力学知识库 > 问答 > 编程问答 >

count the number of employees in certain department in sql

问题描述:

I have a problem about writing a SQL query on counting the number of employees.

Here is the question: There are 2 tables,

T1, the attributes are E_ID(primary key), E_Name, DpartmentID;

T2, DepartmentID(primary key), DepartmentName

Mow i want to count the number of employees in technology department, should i write as below?

SELECT COUNT(E_T1.ID)

FROM T1 JOIN T2 ON T1.DepartmentID = T2.DepartmentID

WHERE T2.DepartmentName = 'Technology'

I was asked this question during an interview, but the interviewee said I missed something... can anyone help me with it? thanks!

网友答案:

Your query makes some assumptions:

  • that the department table is unique on name.
  • that all departments has employees assigned to them.

I would write that query as

select dept.DepartmentId as department_id ,
       sum(case when emp.E_ID is not null then 1 else 0 end) as employee_count ,
from      T2 dept
left join T1 emp  on emp.DepartmentId = dept.DepartmentId
where dept.DepartmentName = 'Technology'
group by t2.DepartmentId
  • You'll notice that I've inverted the join order to emphasize the fact that you're producing summary information about the department.

  • We do a left [outer] join from the department table to the employee table. This guarantees we get at least one row for every department, even if there are no employees assigned to it.

  • Then we use sum() with a *discriminant function to product the count.

Another approach you could take might be

You should try

select dept.DepartmentId as department_id ,
       ( select count(*)
         from T1 emp
         where emp.DepartmentId = dept.DepartmentId
       ) as employee_count
from      T2 dept
where dept.DepartmentName = 'Technology'
group by dept.DepartmentId

Whether this gets a better execution plan than my first solution is dependent on all sorts of factors in your database. The only way to tell would be to check the execution plan against production-like data.

Where this query is simpler, more (or less) elegant than my first solution is subjective. This solution does contain a correlated subquery which [theoretically] means that the subquery has to be executed once for every row in the outer result set — which is presumably 1, so it should be wash from an execution plan perspective. Of course, any decent query optimizer out to be able to refactor it into a join anyway ... but I digress.

分享给朋友:
您可能感兴趣的文章:
随机阅读: