Counting NULLs and sub-sets of data

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 …

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

MultipleResultSets

… 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 …

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

SingleResultSet

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.

Leave a Reply

%d bloggers like this: