Select query with count columns

user_4775353 0
Viewed 28

I need to make a select query of employees and add two columns that consists of a count of how many items they have from another table.

I tried to get it to work with joins but couldn't get it to work. I finally got it to work with subqueries, but would like to know if there is a way to do it with Joins.

This is the code I got to work:

Select e.Id, e.Name, e.Description,
(select COUNT(*) from Equipment WHERE EmployeeId = e.Id and TypeId = 1) as 'SmartphoneCount',
(select COUNT(*) from Equipment WHERE EmployeeId = e.Id and TypeId = 2) as 'labtopCount'
FROM Employees as e

Which results in the following output:

enter image description here

Is there a better way to get the same output?

sql-server sql
2 Answers

You method is fine. However, I would also suggest using apply with a conditional aggregation:

Select e.Id, e.Name, e.Description, eq.*
from Employees as e cross apply
     (select sum(case when typeId = 1 then 1 else 0 end) as SmartPhoneCount,
             sum(case when typeId = 2 then 1 else 0 end) as LapTopCount
      from Equipment eq
      where eq.EmployeeId = e.id
     ) eq;
user_11440350

I believe something like this could work?

SELECT 
e.Id, 
e.Name, 
e.Description,
SUM(CASE WHEN b.TypeID = 1 then 1 else 0 END) as 'SmartphoneCount',
SUM(CASE WHEN b.TypeID = 2 then 1 else 0 END) as 'labtopCount'

FROM Employees as e
LEFT JOIN Equipment b ON e.ID = b.EmployeeId

GROUP BY 1, 2, 3
user_151094270
Related