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.
