SQL Select with count() from another table?

Hi.
I'd like to select * from an Department table and also include a column that is a count() of Employees in each Department.    The Department table (simplified) includes a unique Dept_ID and Description.  Each Employee in the [Emp] table has a "Dept_ID" column, indicating which department the employee is in.

So, in the end, I'd like a resultset something like:

Dept_ID   Description     Emp_Count
-------   -------------   ---------
1         Dept 1 A/P             12
2         Dept 2 Office           8
3         Dept 3 A/R             11

Open in new window


 Is that possible?  I was trying to use a "select" statement in a join, but am doing something wrong ...
-- Thanks
Rob RudloffIT Development SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented: 
select d.dept_id, d.description, count(e.*) Emp_count
from
departments d join
employees  e on  d.Dept_id = e.Dept_id
group by d.dept_id, d.description

Open in new window

Jim HornSQL Server Data DudeCommented:
<air code.  I do my own stunts too> 
SELECT d.Dept_ID, d.Description, COUNT(e.Dept_ID) as Emp_Count
FROM Department d
   LEFT JOIN Employees e ON d.DEPT_ID = e.Dept_ID
GROUP BY d.Dept_ID, d.Description
ORDER BY d.Dept_ID

Open in new window

For a demo check out my article SQL Server GROUP BY Solutions.

<edited after initial post>
dsackerContract ERP Admin/ConsultantCommented:
Perhaps the following is along the lines of what you're looking for:

SELECT  dept.*,
        empCount
FROM    departments d
LEFT JOIN ( SELECT dept, COUNT(1) AS empCount
            FROM   employees
            GROUP BY dept ) e
ON      e.dept = dept.dept

Open in new window

This selects * (everything) from your department table, and adds on the employee count at the end.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
awking00Information Technology SpecialistCommented:
Just clarifying dslacker's column names and aliases (no points).
select d.*, e.empcount
from departments d
left join
(select dept_id, count(*) empcount from employees group by dept_id) e
on d.id = e.dept_id;
Rob RudloffIT Development SpecialistAuthor Commented:
I like dsacker's solution -- it returns all columns with a "D.*" without having to do a "group by" on all the columns.   (In my reality, there are 30+ columns in that table).  
Awking00 changed "count(1)" to "count(*)"  which seemed to have no net change -- I'm curious why the results are the same?
I couldn't get Kyle's to work -- it gives me an  "Incorrect syntax near * "
PortletPaulEE Topic AdvisorCommented:
>>"I'm curious why the results are the same?"

The COUNT() function increments by 1 whenever it encounters a NON-NULL value.

Hence, COUNT(1) or COUNT(*) will simply increment by 1 for each row in the employee table (grouped by department).

This "count only for non-null values" behaviour is extremely useful, BUT you need to understand it.

For example here is some data

tab1
colA colB colC
xxx  yyy   zzz
xxx  yy1  NULL

select count(*) from tab1 (result is 2)

select count(1) from tab1 (result is 2)

select count(colA) from tab1 (result is 2)

select count(colB) from tab1 (result is 2)

select count(colC) from tab1 (result is 1)  <<<!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.