SQL Select with count() from another table?

Rob Rudloff
Rob Rudloff used Ask the Experts™
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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle AbrahamsSenior .Net Developer

select d.dept_id, d.description, count(e.*) Emp_count
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 Dude
Most Valuable Expert 2013
Author of the Year 2015

<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

Open in new window

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

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

SELECT  dept.*,
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

awking00Information Technology Specialist

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 Specialist


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 Advisor
Most Valuable Expert 2014
Awarded 2013

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

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)  <<<!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial