I need to get number of employees per building-CodePudding

enter image description here

This gets me the number of employees per department

SELECT department, COUNT(idEmployees) empCount
FROM employees
GROUP BY department;

CodePudding user response：

You could join on departments to get the buildingID, and then group by that:

SELECT   buildingID, COUNT(*)
FROM     employees e
JOIN     department d ON e.department = d.departmentID
GROUP BY buildingID

CodePudding user response：

i think this is the solution

select d.buildingID, count(e.ID)
from department d
inner join employees e  
on d.departmentID = e.department
group by d.buildingID

CodePudding user response：

This question can be answered in two ways. Firstly, if all employees of a department must be attached in a building. Secondly few employees of a department are attached to a building.

For one where employees are attached to a building it's mandatory

SELECT d.buildingId
     , COUNT(e.id) count_employee
FROM departments d
INNER JOIN employees e
        ON d.departmentid = e.department
GROUP BY d.buildingId

For second where employees are attached to a building but it's optional. In that case LEFT JOIN is used.

SELECT b.buildingId
     , COALESCE(t.count_employee, 0) count_employee
FROM building b
LEFT JOIN (SELECT d.buildingId
                , COUNT(e.id) count_employee
           FROM departments d
           INNER JOIN employees e
                   ON d.departmentid = e.department
           GROUP BY d.buildingId) t
       ON b.buildingId = t.buildingId

If a building is attached with multiple departments and one employee is assigned with multiple departments then count building wise same employee is only onetime not multiple times. In that case DISTINCT keyword is used inside COUNT().

SELECT d.buildingId
     , COUNT(DISTINCT e.id) count_employee
FROM departments d
INNER JOIN employees e
        ON d.departmentid = e.department
GROUP BY d.buildingId

CodePudding user response：

Assuming that the column deparment in the Table Employees is the FK departmentID:

select dpt.buildingId, Count(emp.Id) 
from Employees (nolock) emp 
join Departments (nolock) dpt on dpt.departmentID = emp.departmentID
group by dpt.buildingID