September 25, 2019 at 7:07 am
There is a table EmployeeCategory having categotytypes 1, 2, 3,4.
For only EmployeeCategory 1 & 2 only, there are records in the Employee table now.
Now the count is showing not showing for the 3 & 4 as no records are there, when using COUNT().
How to show the count as 0 even if the records are absent for the category 3 &4?
LEFT JOIN is not working as I havetried that.
September 25, 2019 at 7:18 am
Kindly share your query.
September 25, 2019 at 9:29 am
EmployeeMaster has following columns EmployeedID, EmployeeName, EmpCategoryID, EmpLocationID
EmployeeCategory has following columns EmpCategoryID, EmpCategoryName, EmpCategoryDescription
EmployeeLocation has following columns EmpLocationID, EmpLocationName
select C.EmpLocationName, B.EmpCategoryName, count(A.EmpCategoryID) as CategoryCount
FROM EmployeeMaster A
INNER JOIN EmployeeCategory B ON A.EmpCategoryID = B.EmpCategoryID
INNER JOIN EmployeeLocation C ON A.EmpLocationID = C.EmpLocationID
where ISNULL(A.IsDeleted,0) = 0
Group by C.EmpLocationName, B.EmpCategoryName
September 25, 2019 at 9:31 am
For only EmployeeCategories 1 & 2, there are records in the Employee Master table now.
Now the count is not showing for the 3 & 4, as there are no records in Employee Master , when using COUNT().
How to show the count as 0, even if the records are absent for the category 3 &4?
September 25, 2019 at 10:19 am
Put EmployeeCategory first in your query, and LEFT JOIN it to EmployeeMaster.
John
September 25, 2019 at 10:32 am
Kindly write as a query so that I can understand correctly. The result I need is as below:
Kiev SystemAdmin 11
Kiev WindowsAdmin 5
Kiev Supervisor 0
Kiev ContractWorker 0
Moscow SystemAdmin 7
Moscow WindowsAdmin 0
Moscow Supervisor 0
Moscow ContractWorker 0
Amsterdam SystemAdmin 5
Amsterdam WindowsAdmin 9
Amsterdam Supervisor 0
Amsterdam ContractWorker 0
September 25, 2019 at 11:47 am
Try this.
select C.EmpLocationName, B.EmpCategoryName, count(A.EmpCategoryID) as CategoryCount
FROM EmployeeMaster A
LEFT JOIN EmployeeCategory B ON A.EmpCategoryID = B.EmpCategoryID
INNER JOIN EmployeeLocation C ON A.EmpLocationID = C.EmpLocationID
where ISNULL(A.IsDeleted,0) = 0
Group by C.EmpLocationName, B.EmpCategoryName
September 25, 2019 at 1:54 pm
Try this.
select C.EmpLocationName, B.EmpCategoryName, count(A.EmpCategoryID) as CategoryCount
FROM EmployeeMaster A
LEFT JOIN EmployeeCategory B ON A.EmpCategoryID = B.EmpCategoryID
INNER JOIN EmployeeLocation C ON A.EmpLocationID = C.EmpLocationID
where ISNULL(A.IsDeleted,0) = 0
Group by C.EmpLocationName, B.EmpCategoryName
That's not what he needs - EmployeeCategory needs to go first so that all the employee categories are preserved in the result set. May also need a LEFT JOIN to EmployeeLocation, in case there are any locations in that table that don't appear in EmpoyeeMaster.
I was hoping the original poster would have a go at writing it himself rather than let someone else do it for him. We're here to help people - but not to do their job for them.
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply