{"id":172,"date":"2018-12-15T18:00:59","date_gmt":"2018-12-15T18:00:59","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=172"},"modified":"2018-12-15T17:09:35","modified_gmt":"2018-12-15T17:09:35","slug":"warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/","title":{"rendered":"Warning: Null value is eliminated by an aggregate or other SET operation"},"content":{"rendered":"

In data warehousing where aggregation (GROUP BY) is one of the most common things done in queries, we often get the warning:<\/p>\n

Warning: Null value is eliminated by an aggregate or other SET operation<\/pre>\n
In most cases what is happening is harmless. But what is happening?<\/p>\n
NULL in aggregation<\/h1>\n
First we have to better understand NULL<\/code>, I’ve already looked at Managing update differences: NULL and NOT NULL<\/a>. NULL<\/code> is both amazingly useful, and from time to time the thorn in the side of anyone working with databases.<\/p>\n
Have a look at the below example:<\/p>\n
WITH data AS (\nSELECT CAST(1 AS INT) AS c1, CAST(1 AS INT)    AS c2 UNION ALL\nSELECT CAST(1 AS INT) AS c1, CAST(2 AS INT)    AS c2 UNION ALL\nSELECT CAST(1 AS INT) AS c1, CAST(NULL AS INT) AS c2\n)\nSELECT SUM(c1)   AS [SUM_c1],\n       COUNT(c1) AS [COUNT_c1],\n\u00a0\u00a0\u00a0\u00a0   SUM(c2)   AS [SUM_c2],    -- Note: This causes the \"Warning\"\n       COUNT(c2) AS [COUNT_c2],  -- Note: This causes the \"Warning\"\n\u00a0\u00a0\u00a0\u00a0   COUNT(*)  AS [COUNT_all]  -- Note: This gives a different result to above\n  FROM data\n;<\/pre>\n\n\n\n\n
SUM_c1<\/strong><\/td>\nCOUNT_c1<\/strong><\/td>\nSUM_c2<\/strong><\/td>\nCOUNT_c2<\/strong><\/td>\nCOUNT_all<\/strong><\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n3<\/td>\n2<\/td>\n3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Note: the warning comes from this line:\u00a0SUM(c2)<\/code><\/p>\n
The warning is telling us that the NULL<\/code> value is being eliminated (thrown out) by the aggregation. So in this instance the value might as well be 0<\/code>.<\/p>\n
The danger of NULL in aggregation<\/h2>\n
NULL in COUNT<\/h3>\n
In the above example, the more interesting threat to processing and results is the COUNT(c2)<\/code> as when specifying a column with a NULL<\/code>, the value is discarded and no count is returned.<\/p>\n
Note that COUNT(*)<\/code> is safe when trying to get a count of rows in an aggregation.<\/p>\n
NULL in addition<\/h3>\n
NULL<\/code> in addition (as part of a row level expression) when added to another number returns NULL<\/code>, see this example:<\/p>\n
SELECT 1 + 1                 AS [c1],\n       1 + NULL              AS [c2], -- The problem\n       1 + COALESCE(NULL, 0) AS [c3]  -- The simple solution\n;<\/pre>\n\n\n\n\n
c1<\/strong><\/td>\nc2<\/strong><\/td>\nc3<\/strong><\/td>\n<\/tr>\n
2<\/td>\nNULL<\/td>\n1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
On a row by row basis we will see it quickly. But as shown with the SUM<\/code> above (generating the warning) when combined with an aggregation that NULL<\/code> can get easily lost:<\/p>\n
WITH data AS (\nSELECT 1 + 1                 AS [c1],\n       1 + NULL              AS [c2], -- The problem\n       1 + COALESCE(NULL, 0) AS [c3]  -- The simple solution\nUNION ALL\nSELECT 1                     AS [c1],\n       1                     AS [c2],\n       1                     AS [c3]\n)\nSELECT SUM([c1]) AS [c1_sum],\n       SUM([c2]) AS [c2_sum], -- This is incorrect\n       SUM([c3]) AS [c3_sum]  -- This is what we want\n  FROM data\n;<\/pre>\n\n\n\n\n
c1_sum<\/strong><\/td>\nc2_sum<\/strong><\/td>\nc3_sum<\/strong><\/td>\n<\/tr>\n
3<\/td>\n1<\/td>\n2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
In the above example, the value for c2_sum<\/code> is probably incorrect for what most people would want\/expect as it’s not including the first value being added.<\/p>\n
For c3<\/code> (and c3_sum<\/code>) the use of COALESCE<\/code> replaces the NULL<\/code> with 0<\/code> and returns the expected result.<\/p>\n","protected":false},"excerpt":{"rendered":"
In data warehousing where aggregation (GROUP BY) is one of the most common things done in queries, we often get the warning: Warning: Null value is eliminated by an aggregate or other SET operation In most cases what is happening is harmless. But what is happening? NULL in aggregation First we have to better understand…<\/p>\n
Read More  Read More<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4],"tags":[17],"class_list":["post-172","post","type-post","status-publish","format-standard","hentry","category-databases","category-sql-server","tag-null"],"yoast_head":"\nWarning: Null value is eliminated by an aggregate or other SET operation - Rows Across The Lake<\/title>\n<meta name=\"description\" content=\"Understanding the warning "Warning: Null value is eliminated by an aggregate or other SET operation"\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Warning: Null value is eliminated by an aggregate or other SET operation - Rows Across The Lake\" \/>\n<meta property=\"og:description\" content=\"Understanding the warning "Warning: Null value is eliminated by an aggregate or other SET operation"\" \/>\n<meta property=\"og:url\" content=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/\" \/>\n<meta property=\"og:site_name\" content=\"Rows Across The Lake\" \/>\n<meta property=\"article:published_time\" content=\"2018-12-15T18:00:59+00:00\" \/>\n<meta name=\"author\" content=\"david\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:site\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"david\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/12\\\/15\\\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/12\\\/15\\\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\\\/\"},\"author\":{\"name\":\"david\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"headline\":\"Warning: Null value is eliminated by an aggregate or other SET operation\",\"datePublished\":\"2018-12-15T18:00:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/12\\\/15\\\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\\\/\"},\"wordCount\":291,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"keywords\":[\"NULL\"],\"articleSection\":[\"Databases\",\"SQL Server\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/12\\\/15\\\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/12\\\/15\\\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\\\/\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/12\\\/15\\\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\\\/\",\"name\":\"Warning: Null value is eliminated by an aggregate or other SET operation - Rows Across The Lake\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\"},\"datePublished\":\"2018-12-15T18:00:59+00:00\",\"description\":\"Understanding the warning \\\"Warning: Null value is eliminated by an aggregate or other SET operation\\\"\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/12\\\/15\\\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/12\\\/15\\\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/12\\\/15\\\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Warning: Null value is eliminated by an aggregate or other SET operation\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\",\"name\":\"Rows Across The Lake\",\"description\":\"Data & Databases\",\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\",\"name\":\"david\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"caption\":\"david\"},\"logo\":{\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Warning: Null value is eliminated by an aggregate or other SET operation - Rows Across The Lake","description":"Understanding the warning \"Warning: Null value is eliminated by an aggregate or other SET operation\"","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/","og_locale":"en_GB","og_type":"article","og_title":"Warning: Null value is eliminated by an aggregate or other SET operation - Rows Across The Lake","og_description":"Understanding the warning \"Warning: Null value is eliminated by an aggregate or other SET operation\"","og_url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/","og_site_name":"Rows Across The Lake","article_published_time":"2018-12-15T18:00:59+00:00","author":"david","twitter_card":"summary_large_image","twitter_creator":"@d_roman_h","twitter_site":"@d_roman_h","twitter_misc":{"Written by":"david","Estimated reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/#article","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/"},"author":{"name":"david","@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"headline":"Warning: Null value is eliminated by an aggregate or other SET operation","datePublished":"2018-12-15T18:00:59+00:00","mainEntityOfPage":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/"},"wordCount":291,"commentCount":0,"publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"keywords":["NULL"],"articleSection":["Databases","SQL Server"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/","url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/","name":"Warning: Null value is eliminated by an aggregate or other SET operation - Rows Across The Lake","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/#website"},"datePublished":"2018-12-15T18:00:59+00:00","description":"Understanding the warning \"Warning: Null value is eliminated by an aggregate or other SET operation\"","breadcrumb":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/12\/15\/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/datablog.roman-halliday.com\/"},{"@type":"ListItem","position":2,"name":"Warning: Null value is eliminated by an aggregate or other SET operation"}]},{"@type":"WebSite","@id":"https:\/\/datablog.roman-halliday.com\/#website","url":"https:\/\/datablog.roman-halliday.com\/","name":"Rows Across The Lake","description":"Data & Databases","publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/datablog.roman-halliday.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b","name":"david","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","caption":"david"},"logo":{"@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g"}}]}},"_links":{"self":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/comments?post=172"}],"version-history":[{"count":2,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/172\/revisions"}],"predecessor-version":[{"id":244,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/172\/revisions\/244"}],"wp:attachment":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/media?parent=172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/categories?post=172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/tags?post=172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}