Monday, 26 August 2013

IS NULL or NOT IN to consider null values in the count

IS NULL or NOT IN to consider null values in the count

When the run the below query with t.status is NULL it gives me different
count (which i think is incorrect).
How do I get the count by NOT IN ('Cancelled', 'Duplicate') but include
NULL value rows in the count
For the below query i get a count of 423
select count(*) from QC10.DEFECTS d
inner join QC10.DEFECTS_TRAN t on D.RECORD_ID=T.DEFECT_ID_FK_DT
where
T.LATEST_RECORD='Y'
and
(t.status NOT IN ('Cancelled', 'Duplicate'))
But when I run the below query i get 8530 as the count
select count(*) from QC10.DEFECTS d
inner join QC10.DEFECTS_TRAN t on D.RECORD_ID=T.DEFECT_ID_FK_DT
where
T.LATEST_RECORD='Y'
and
(t.status NOT IN ('Cancelled', 'Duplicate'))
or (t.status IS NULL)

No comments:

Post a Comment