Quick Tip: Counting NULLs and sub-sets of data

You have been asked to produce a chart which will visualize a few data points.

• Total work orders.
• Total closed work orders (work orders with EndDate populated).
• Total open work orders (work orders with a NULL EndDate).
• Total work orders with excessive scrap material (work orders with ScrappedQty >= 100).

You can easily count each of these result sets …

[sql]USE AdventureWorks2012
GO
SELECT COUNT(*) [WorkOrder_Count]
FROM Production.WorkOrder wo

SELECT COUNT(*) [EndDate_Count]
FROM Production.WorkOrder wo
WHERE wo.EndDate IS NOT NULL

SELECT COUNT(*) [NULL_EndDate_Count]
FROM Production.WorkOrder wo
WHERE wo.EndDate IS NULL

SELECT COUNT(*) [ExcessiveScrap_Count]
FROM Production.WorkOrder wo
WHERE wo.ScrappedQty >= 100[/sql]

… but a single query using a single connection would perform much better when returning data for a chart on something like a web-based dashboard. The trouble is that the COUNT() function will not count NULL values by themselves. Instead, it will count all records in the result-set or it will count all non-NULL records based on a given column.

To get around this limitation, you can use the SUM() function with a CASE statement …

[sql]SELECT COUNT(*) [WorkOrder_Count]
,COUNT(wo.EndDate) [EndDate_Count]
,SUM(CASE WHEN wo.EndDate IS NOT NULL THEN 1 ELSE 0 END) [EndDate_Count_AnotherWay]
,SUM(CASE WHEN wo.EndDate IS NULL THEN 1 ELSE 0 END) [NULL_EndDate_Count]
,SUM(CASE WHEN wo.ScrappedQty >= 100 THEN 1 ELSE 0 END) [ExcessiveScrap_Count]
FROM Production.WorkOrder wo [/sql]

Now you have all of your data points in a single query, with a single record, which is fast and simple for a service or data visualization tool to consume.