To show the count as 0 if no record is present or NULL

GeoJul · September 25, 2019, 7:10am

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 have tried that.

harishgg1 · September 25, 2019, 9:47am

hi

please help me understand

count should show only if all categories 1 2 3 4 are present
if anything missing category example 1 3 4 or example 1 2 3 then count should be zero

what about if category 5 is there ???

thanks

GeoJul · September 25, 2019, 9:52am

EmployeeMaster has following columns EmployeedID, EmployeeName, EmpCategoryID, EmpLocationID

EmployeeCategory has following columns EmpCategoryID, EmpCategoryName, EmpCategoryDescription

EmployeeLocation has following columns EmpLocationID, EmpLocationName

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?

Now, only 4 categories are there.

Query I have written is this, which is not showing count for category 3 & 4.

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

harishgg1 · September 25, 2019, 10:05am

what you can do !!!

if category 1 then 1 else 0
if category 2 then 1 else 0
if category 3 then 1 else 0
if category 4 then 1 else 0

sum of category = 4 then show count
sum of category < 4 then show count 0

hope this helps

GeoJul · September 25, 2019, 10:29am

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

harishgg1 · September 25, 2019, 10:29am

another thing you can do is

if category is always 1 or 2 or 3 or 4 .... no 5 no 6 etc

then
select case when count(distinct category ) = 4 then count(*) else 0 end

hope this helps

harishgg1 · September 25, 2019, 10:59am

hi

please provide drop create sample data ... like below
we can work on the SQL and give you answer

please click arrow ot the left for "drop create sample data "

drop table employeemaster 
go 
drop table employeecategory 
go 
drop table employeelocation 
go 

create table employeemaster
(
empcategoryid int ,
emplocationid int , 
isdeleted int 
)
go 

insert into employeemaster select 1,1,0
insert into employeemaster select 2,1,0
insert into employeemaster select 3,1,0
insert into employeemaster select 4,1,0
insert into employeemaster select 5,1,1
insert into employeemaster select 6,1,null 
insert into employeemaster select 7,1,1
insert into employeemaster select 1,2,0
insert into employeemaster select 2,2,0
insert into employeemaster select 3,2,0
insert into employeemaster select 4,2,0
insert into employeemaster select 5,2,1
insert into employeemaster select 6,2,null 
insert into employeemaster select 7,2,1
go 


create table employeecategory
(
empcategoryid int ,
empcategoryname varchar(100) 
)
go 

insert into employeecategory select 1,'SystemAdmin'
insert into employeecategory select 2,'WindowsAdmin'
insert into employeecategory select 3,'Supervisor'

create table employeelocation
(
emplocationid int, 
emplocationname varchar(100) 
)
go 
insert into employeelocation select  1 ,'Kiev'
insert into employeelocation select  2 ,'Moscow'
insert into employeelocation select  3 ,'Amsterdam'
go 

select * from  employeemaster go 
select * from  employeecategory go 
select * from  employeelocation go

GeoJul · September 25, 2019, 11:52am

I tried this way also, but no luck. Its not returning the way I want. Any improvements, please suggest.

	select C.EmpLocationName, 
	count(A.EmpCategoryID) as CategoryCount  INTO #Tmp
	FROM EmployeeMaster A 
	INNER JOIN EmployeeLocation C ON A.EmpLocationID = C.EmpLocationID
	where ISNULL(A.IsDeleted,0) = 0
	Group by  C.EmpLocationName

	
	SELECT T.EmpLocationName, C.EmpCategoryName,
	CASE WHEN COUNT(ISNULL( T.CategoryCount,0)) = 0 THEN 0 ELSE COUNT( T.CategoryCount) END as Counts 
	FROM EmployeeCategory C 
	LEFT JOIN #Tmp T ON  C.EmpCategoryID = T.EmpCategoryID
	GROUP BY T.EmpLocationName, C.EmpCategoryName

uberbloke · September 25, 2019, 2:11pm

Shamelessly taking @harishgg1 good work in creating the dummy data....

if this is not what you need, please adapt the table creation and data population to your situation and based on the populated data, what your expected output should be

USE tempdb
GO

set nocount ON
go

drop table IF EXISTS employeemaster 
go 
drop table IF EXISTS employeecategory 
go 
drop table IF EXISTS employeelocation 
go 

create table employeemaster
(
empcategoryid int ,
emplocationid int
)

create table employeecategory
(
empcategoryid int ,
empcategoryname varchar(100) 
)

insert into employeecategory select 1,'SystemAdmin'
insert into employeecategory select 2,'WindowsAdmin'
insert into employeecategory select 3,'Supervisor'
insert into employeecategory select 3,'ContractWorker'


create table employeelocation
(
emplocationid int, 
emplocationname varchar(100) 
)
insert into employeelocation select  1 ,'Kiev'
insert into employeelocation select  2 ,'Moscow'
insert into employeelocation select  3 ,'Amsterdam'


insert into employeemaster select 1,1
insert into employeemaster select 1,1
insert into employeemaster select 1,1
insert into employeemaster select 1,2
insert into employeemaster select 1,2
insert into employeemaster select 1,2
insert into employeemaster select 1,2
insert into employeemaster select 1,2
insert into employeemaster select 2,1
insert into employeemaster select 3,1
insert into employeemaster select 4,1
insert into employeemaster select 5,1
insert into employeemaster select 6,1 
insert into employeemaster select 7,1
insert into employeemaster select 1,2
insert into employeemaster select 2,2
insert into employeemaster select 3,2
insert into employeemaster select 4,2
insert into employeemaster select 5,2
insert into employeemaster select 6,2 
insert into employeemaster select 7,2
insert into employeemaster select 3,1
insert into employeemaster select 3,1
insert into employeemaster select 1,3
insert into employeemaster select 1,3
insert into employeemaster select 1,3


go 

-- SET STATISTICS IO, TIME ON

--select * from  employeemaster go 
--select * from  employeecategory go 
--select * from  employeelocation go


/*
	one way to get what you want
*/
; with headers as (
	select * from employeecategory
	cross join employeelocation
)
select emplocationname, empcategoryname, counts.total
from headers
cross apply (
	select count(*) as total
	from employeemaster 
	where emplocationid = headers.emplocationid
	and empcategoryid = headers.empcategoryid

) as counts
ORDER BY emplocationname, empcategoryname

/*
	an alternative
*/

select el.emplocationname, ec.empcategoryname, count(em.empcategoryid) from employeecategory ec
cross join employeelocation el
LEFT OUTER JOIN employeemaster em
	on ec.empcategoryid = em.empcategoryid
	and el.emplocationid = em.emplocationid
group by el.emplocationname, ec.empcategoryname
ORDER BY el.emplocationname, ec.empcategoryname