Simple Query Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple Query Question

Author

Topic

axterix
Starting Member

2 Posts

Posted - 2009-09-21 : 11:56:04

I all sql noob here..so bare with me..please :)

Given 2 tables (EMPLOYEE,DEPARTMENT)
where:

EMPLOYEE has columns (lastname, departmentID)
DEPARTMENT has columns(departmentID, departmentname)

I want to be able to list all departments with their corrisponding count of employees, including departments with 0 employees.

All I have been able to come with is the following:
however, this is incorrect as it will show a count of 1 for departments with 0 employees.


select departmentname, count(*)
 from employee
 right outer join department
 using (departmentid)
group by departmentid

Any pointers?

gavakie
Posting Yak Master

221 Posts

Posted - 2009-09-21 : 11:58:40

Select departmentID, count(lastname)
From Department D
Left join Employee E on d.departmentID = E.departmentID
group by departmentid

axterix
Starting Member

2 Posts

Posted - 2009-09-21 : 12:16:24

quote:
Originally posted by gavakie

Select departmentID, count(lastname)
From Department D
Left join Employee E on d.departmentID = E.departmentID
group by departmentid

this too does not work :( , it suffers from the same problem, the departments with zero employees will show up with an employee count of 1...i.e. the entire problem stems from group by/count in order for count to count anything it must return a row..and indeed the row comes back with nil in the record..but the count is applied to the row..resulting in the count of 1...I keep thinking I need to somehow use the sum function somehow..

gavakie
Posting Yak Master

221 Posts

Posted - 2009-09-21 : 12:47:36

Select departmentID, Sum(Case when lastname is null then 0 else 1) as emloyee_count
From Department D
Left join Employee E on d.departmentID = E.departmentID
group by departmentid

gavakie
Posting Yak Master

221 Posts

Posted - 2009-09-21 : 12:48:01

sorry dont forget the "end" in the case statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources