Geek asks and answers

SQL COUNT (*) returns an invalid response

The following script should return the name of the departments and the number of employees located in these departments, in the departments of marketing, executive authority and sales there is "0", and instead of "0" the value "1 'is returned. How can I fix it?

select Department, Departments.DepartmentID, count(*) as 'NumOfEmps' 
from Departments
left join Employees
on   Employees.DepartmentID = Departments.DepartmentID
group by Departments.DepartmentID,Department

enter image description here

+3
source share
2 answers

Do not use to Count(*)count the subject that you want to count, namely employees.

Count(*)counts the entire line. Since there will always be at least one entry for each department in departments, when you count (*), you will always get at least 1

SELECT d.Department, d.DepartmentID, count(e.EmployeeID)
FROM Departments d
    LEFT JOIN employees e
    ON d.DepartmentID = e.DepartmentID
GROUP BY 
 d.Department, d.DepartmentID

Demo

+5
source

. , , ( ..), :

SELECT Department, Departments.DepartmentID, t.NumOfEmps
FROM Departments
LEFT JOIN (SELECT DepartmentID, count(*) as 'NumOfEmps'
           FROM Employees
           GROUP BY DepartmentID) t
  ON t.DepartmentID = Departments.DepartmentID

, . , . , .

+6

More articles:


All Articles