SQL Select with count() from another table?

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

Do more with

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

Commented: 
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 Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
<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>
Contract ERP Admin/Consultant
Commented:
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.
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!

Find out now!
awking00Information Technology Specialist

Commented:
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

Author

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

Commented:
>>"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)  <<<!!!

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