how to count nulls

Question

Post reply

how to count nulls

robmays

Newbie

Points: 3
More actions
October 14, 2005 at 4:28 am

#90396

"I am trying to get this query to pull out rows where the customer has part installed work, ie the customer has many orders but the order is not complete until all work is installed, so for example the customer may have 3 jobs but if one of the jobs has an Installed Date of NULL but at least one job is installed then i need the record to come out.
here is my query so far, but i dont know how to count how many of the jobs are not installed???
SELECT     TOP 100 PERCENT dbo.Customers.CustomerID, COUNT(dbo.Sales.CustomerID) AS JobCount,SaleItems.InstalledDate
FROM         dbo.Customers INNER JOIN
                      dbo.Sales ON dbo.Customers.CustomerID = dbo.Sales.CustomerID INNER JOIN
                      dbo.SaleItems ON dbo.Sales.SaleID = dbo.SaleItems.SaleID
WHERE     (dbo.SaleItems.TypeOfJob <> 7) AND (dbo.SaleItems.CancelledDate IS NULL)
GROUP BY dbo.Customers.CustomerID, dbo.Customers.Title, dbo.Customers.LastName, dbo.Customers.HouseNo, dbo.Customers.Street, dbo.Customers.Area,
                      dbo.Customers.City, dbo.Customers.County, dbo.Customers.Postcode,SaleItems.InstalledDate
HAVING      (COUNT(dbo.Sales.CustomerID) > 1) and (count(SaleItems.InstalledDate = null)> 0)
ORDER BY dbo.Customers.CustomerID

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32473 More actions · Answer 1

X = null is always Unknown

Compare to NULL with X IS NULL

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

NULL does not equal NULL. NULL is the absence of information, not a value.

Try this:

Instead of: (count(SaleItems.InstalledDate = null)> 0)

Try: COUNT( CASE

WHEN SaleItems.InstalledDate IS NULL

THEN 1

ELSE 0

END) > 0

I wasn't born stupid - I had to study.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

When you have allow NULL column you can only use one aggregate function in SQL Server and that is COUNT (*) because all other aggregate functions ignore NULLs. Try the link below for more info. Hope this helps.

http://www.akadia.com/services/dealing_with_null_values.html

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Thanks. Did not realize COUNT included NULLs. Thought all aggregates disregarded them. That is important to keep in mind!!

I wasn't born stupid - I had to study.