how to count null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to count null

Author

Topic

robmays
Starting Member

3 Posts

Posted - 2005-10-14 : 06:01:20

rob writes "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
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.InstalledDate IS NULL) 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
HAVING (COUNT(dbo.Sales.CustomerID) > 1)
ORDER BY dbo.Customers.CustomerID"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 06:13:00

Instead of

COUNT(dbo.Sales.CustomerID) AS JobCount

Try this

Sum(Case when dbo.SaleItems.InstalledDate is null then 0 else 1 end) AS JobCount

Otherwise post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-14 : 07:40:08

>>but i dont know how to count how many of the jobs are not installed???
just reverse madhivanan's CASEs of 0 and 1 to count the NULLs.

or something like this:


select c.customerid
       count(*) as [unInstalledCount]
from   dbo.Customers c
join   dbo.Sales s 
             on s.customerid = c.customerid
join dbo.salesitems si 
             on si.salesid = s.salesid
where  si.installedDate is NULL
group by c.customerid

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources