ISSUE :
 
Profile photo
Submitted by Mark Mazerski
on August 20th, 2012 at 17:04:47 EST

count 0 on empty or null values in oracle

Hi,
I have to put some count in table that has null or empty values how do i do it in oracle?
I am able to get right result for not null values.
Any suggestion appreciated.
Thanks,
MM


Profile photo
Replied by Yogaraj Khanal
on August 22nd, 2012 at 14:36:35 EST
Try this sample
SELECT Region.Region_Name, NVL(SubTotal.Total,0) As SalesTotal
From Region
Left Join
(SELECT Region_ID, Count(Sales.Amount) As Total
From Sales
Where Amount > 1000
Group By Region_ID) SubTotal
On Region.Region_id = SubTotal.Region_ID

Profile photo
Replied by Yogaraj Khanal
on August 23rd, 2012 at 11:02:21 EST
This is another trick that i did locally with my data sets
select pro.protocolid,count(pp.patientid) as patientCount
from protocol pro
left join patientprotocol pp
on pro.protocolid = pp.protocolid
where (pp.patientid is not null)
group by pro.protocolid
UNION
select pro.protocolid,count(pp.patientid) as patientCount
from protocol pro
left join patientprotocol pp
on pro.protocolid = pp.protocolid
where (pp.patientid is null)
group by pro.protocolid
Accepted Answer
Accepted
 Answer