{"id":134,"date":"2015-10-13T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/counting-nulls-and-sub-sets-of-data"},"modified":"2026-03-18T21:52:13","modified_gmt":"2026-03-18T21:52:13","slug":"counting-nulls-and-sub-sets-of-data","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2015\/10\/13\/counting-nulls-and-sub-sets-of-data\/","title":{"rendered":"Counting NULLs and sub-sets of data &#8211; SQL Hammer"},"content":{"rendered":"<h2>Quick Tip: Counting NULLs and sub-sets of data<\/h2>\n<p>You have been asked to produce a chart which will visualize a few data points.<\/p>\n<ul>\n<li>Total work orders.<\/li>\n<\/ul>\n<ul>\n<li>Total closed work orders (work orders with EndDate populated).<\/li>\n<\/ul>\n<ul>\n<li>Total open work orders (work orders with a NULL EndDate).<\/li>\n<\/ul>\n<ul>\n<li>Total work orders with excessive scrap material (work orders with ScrappedQty >= 100).<\/li>\n<\/ul>\n<p>You can easily count each of these result sets \u2026<\/p>\n<pre><code class=\"language-\">USE AdventureWorks2012\nGO\nSELECT COUNT(<em>) [WorkOrder_Count]\nFROM Production.WorkOrder wo\n\n<p>SELECT COUNT(<\/em>) [EndDate_Count]\nFROM Production.WorkOrder wo\nWHERE wo.EndDate IS NOT NULL<\/p>\n\n<p>SELECT COUNT(<em>) [NULL_EndDate_Count]\nFROM Production.WorkOrder wo\nWHERE wo.EndDate IS NULL<\/p>\n\n<p>SELECT COUNT(<\/em>) [ExcessiveScrap_Count]\nFROM Production.WorkOrder wo\nWHERE wo.ScrappedQty &gt;= 100<\/code><\/pre>\n<\/p>\n<p><a href=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2015\/10\/MultipleResultSets.png\"><img decoding=\"async\" src=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2015\/10\/MultipleResultSets.png\" alt=\"\" \/><\/a><\/p>\n<p>\u2026 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 <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms175997.aspx#Anchor_3\">COUNT()<\/a>\u00a0function 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.<\/p>\n<p>To get around this limitation, you can use the <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms187810.aspx\">SUM()<\/a> function with a <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms181765.aspx\">CASE<\/a> statement \u2026<\/p>\n<pre><code class=\"language-\">SELECT COUNT(*) [WorkOrder_Count]\n,COUNT(wo.EndDate) [EndDate_Count]\n,SUM(CASE WHEN wo.EndDate IS NOT NULL THEN 1 ELSE 0 END) [EndDate_Count_AnotherWay]\n,SUM(CASE WHEN wo.EndDate IS NULL THEN 1 ELSE 0 END) [NULL_EndDate_Count]\n,SUM(CASE WHEN wo.ScrappedQty &gt;= 100 THEN 1 ELSE 0 END) [ExcessiveScrap_Count]\nFROM Production.WorkOrder wo<\/code><\/pre>\n<p><a href=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2015\/10\/SingleResultSet.png\"><img decoding=\"async\" src=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2015\/10\/SingleResultSet.png\" alt=\"\" \/><\/a><\/p>\n<p>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.<\/p>\n<hr \/>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/hammer-derik-172x140.png\" alt=\"\" \/><strong>Written by Derik Hammer of <a href=\"http:\/\/www.sqlhammer.com\/\">SQL Hammer<\/a><\/strong><\/p>\n<p>Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/high-availability\/\">high-availability<\/a>, <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/general\/disaster-recovery\/\">disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n<p>Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started <a href=\"http:\/\/www.sqlhammer.com\/\">SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n<p>Derik is the owner and lead author of SQL Hammer, a <a href=\"http:\/\/www.sqlhammer.com\/\">Microsoft SQL Server resource<\/a>.<\/p>\n<p>For more information, visit <a href=\"http:\/\/www.sqlhammer.com\/\">http:\/\/www.sqlhammer.com<\/a>. Follow Derik on <a href=\"http:\/\/twitter.com\/SQLHammer\">Twitter<\/a> for <strong>SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-134","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/134","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=134"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/134\/revisions"}],"predecessor-version":[{"id":396,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/134\/revisions\/396"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=134"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=134"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}