SQLTeam.com | Weblogs | Forums

Need to avoid NULL & replace with 0 in the result

Need to avoid NULL & replace with 0 in the result

 SELECT *
FROM (SELECT P.IncidentID, 
			 PT.Penalty,
			 I.Title,
			 COUNT(P.PenaltyID) as Penaltycount
FROM
	PenaltyMaster P 
	INNER JOIN PenaltyTypeMaster PT ON P.PenaltyTypeID = PT.PenaltyTypeID
	LEFT JOIN IncidentsMaster I ON P.IncidentID = I.IncidentID
	WHERE 
	ISNULL(P.IsDeleted, 0) = 0
      GROUP BY 	P.IncidentID, 
			 PT.Penalty,
			 I.Title) AS PenaltycountData
PIVOT( SUM([Penaltycount])   
    FOR Penalty IN ([OneTime],[Recurring])) AS PenaltycountPivot

The result I am getting is as follows:

IncidentID Title OneTime Recurring
10 Incident-1 1 NULL
11 Incident-2 1 1
12 Incident-3 NULL 1

use sqlteam
go

create table #IncidentsMaster(IncidentID int, Title nvarchar(150))
insert into #IncidentsMaster
select 1, 'Light Saber Light too short' union
select 2, 'Warp Speed Actuator Malfunction' 

select * From #IncidentsMaster

create table #PenaltyTypeMaster(PenaltyTypeID int, Penalty nvarchar(150))
insert into #PenaltyTypeMaster
select 1, 'OneTime' union
select 2, 'Recurring' 

select * from #PenaltyTypeMaster
create table #PenaltyMaster(PenaltyID int identity(1,1), IncidentID int, PenaltyTypeID int, IsDeleted bit default((0)))
insert into #PenaltyMaster(IncidentID,PenaltyTypeID)
select 1, 1 union
select 2, 1 union
select 2, 2 




SELECT IncidentID
     , Title
     , OneTime = coalesce(OneTime, 0)
     , Recurring = coalesce(Recurring, 0)
FROM (SELECT P.IncidentID, 
			 PT.Penalty,
			 I.Title,
			 COUNT(P.PenaltyID) as Penaltycount
FROM
	#PenaltyMaster P 
	INNER JOIN #PenaltyTypeMaster PT ON P.PenaltyTypeID = PT.PenaltyTypeID
	LEFT JOIN #IncidentsMaster I ON P.IncidentID = I.IncidentID
	WHERE 
	ISNULL(P.IsDeleted, 0) = 0
      GROUP BY 	P.IncidentID, 
			 PT.Penalty,
			 I.Title) AS PenaltycountData
PIVOT( SUM([Penaltycount])   
    FOR Penalty IN ([OneTime],[Recurring])) AS PenaltycountPivot
1 Like

Instead of using 'SELECT *' in the outer query, you need to specify each column. Then you can use this:

SELECT IncidentID
     , Title
     , OneTime = coalesce(OneTime, 0)
     , Recurring = coalesce(Recurring, 0)
...

The reason you are getting a NULL value is because there are no matching rows for the PIVOT.

Thanks yosiasz for the query & work done behind it.
The query is working exactly what I need.
Thanks jeffw8713 for the explanation.

My original solution was wrong @jeffw8713 answered it. I just gave it some data