Best practice for counting values that contain NULL values

I am wondering if the best way to count a li开发者_JAVA百科st of values that contains NULL values is by doing the following?

COUNT(ISNLL(Date),'')

I ask because I have a while loop that only runs if the count > 0. IT looks like this is working but but is it the proper way? Here is my code:

WHILE SELECT(Count(*)
             FROM 
              (SELECT 1 AS a
                   FROM dbo.tblrecords r
                   LEFT JOIN dbo.tblError e
                GROUP BY r.RecordId, r.MissingCount
                HAVING r.MissingCount > COUNT(DISTINCT(ISNULL(e.[ErrorDate],''))) > 0

If the e.ErrorDate is NULL I do not want it to count this.

---

just add WHERE e.ErrorDate is not null to your query

---

Aggregate functions (COUNT, SUM, ETC) will eliminate nulls so if you want them to be counted, you can either COUNT(*) which will count records, not individual values, or do as you're doing ISNULL/COALESCE to replace null values.

If you want to count only null values, do a count(*) where the value IS NOT NULL as @Derek Kromm suggested.

FWIW, I don't think the DISTINCT is necessary as it may throw off your count if you have multiple nulls that you wish to count as more than one.

---

WHILE (SELECT COUNT(*) FROM sometable WHERE somefield IS NULL) > 0