Select query with count columns

Question

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:

Is there a better way to get the same output?

Gordon Linoff · Accepted Answer · 2021-02-18 19:18:13Z

1

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;

answered Feb 18, 2021 at 19:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

redarah · Accepted Answer · 2021-02-18 19:20:24Z

0

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

answered Feb 18, 2021 at 19:20

redarah

1627 bronze badges

1 Comment

Mark Schultheiss Over a year ago

please don'1 promote using GROUP BY 1, 2, 3 and instead the actual columns by name perhaps

Collectives™ on Stack Overflow

Select query with count columns

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related