XML FeedsWhat is the percentage of null values in a table for a column? This question comes up every now and then and it is pretty easy to answer this question
We will start by creating the following table
- CREATE TABLE #perc ( Column1 INT,Column2 INT,Column3 INT)
- INSERT INTO #perc
- SELECT NULL,1,1
- UNION ALL
- SELECT 1,1,1
- UNION ALL
- SELECT NULL,NULL,1
- UNION ALL
- SELECT NULL,1,NULL
- UNION ALL
- SELECT NULL,1,1
- UNION ALL
- SELECT 1,1,NULL
- UNION ALL
- SELECT NULL,1,1
- UNION ALL
- SELECT 2,1,2
- UNION ALL
- SELECT 3,1,1
There are a couple of ways to calculate this but first we need to understand one thing: COUNT(*) and COUNT(ColumnName) behave differently, COUNT(*) will count NULLS while COUNT(ColumnName) does not!!!
Here is one way to calculate the percentages, you use the following formula
- SUM(CASE WHEN ColumnName IS NULL THEN 1 ELSE 0 END) / COUNT(*)
Run this query below
- SELECT 100.0 * SUM(CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Column1Percent,
- 100.0 * SUM(CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Column2Percent,
- 100.0 * SUM(CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Column3Percent
- FROM #perc
output
Column1Percent Column2Percent Column3Percent 55.555555555555 11.111111111111 22.222222222222
Instead of using SUM and a CASE statement you can also just do the formula below to get the percentage of NULLS
- (COUNT(*) - COUNT(ColumnName)) / COUNT(*)
The query below will return the same output as the query above
- SELECT 100.0 * (COUNT(*) - COUNT(Column1)) / COUNT(*) AS Column1Percent,
- 100.0 * (COUNT(*) - COUNT(Column2)) / COUNT(*) AS Column2Percent,
- 100.0 * (COUNT(*) - COUNT(Column3)) / COUNT(*) AS Column3Percent
- FROM #perc
output
Column1Percent Column2Percent Column3Percent 55.555555555555 11.111111111111 22.222222222222
What if you want to get a percentage of all values in the column? So for example we will take Column3 from the table
- select Column3 from #perc
1 1 1 NULL 1 NULL 1 2 1
As you can see we have
2 rows with a value of NULL = 22.22% (select (2.0/9.0) * 100)
1 row with a value of 2 = 11.11% (select (1.0/9.0) * 100
6 rows with a value of 1 = 22.22% (select (6.0/9.0) * 100)
Here is the query which accomplishes this requirement
- SELECT COALESCE(CONVERT(VARCHAR(50),Column3),'NULL') AS Value,
- COUNT(Column3) AS ValueCount,
- 100.0 * COUNT(*)/(SELECT COUNT(*) FROM #perc ) AS Percentage
- FROM #perc
- GROUP BY Column3
- ORDER BY Percentage DESC
And here is the output
Output
Value ValueCount Percentage 1 6 66.666666666666 NULL 0 22.222222222222 2 1 11.111111111111
That is all, as you can see it is pretty trivial to calculate NULLS in a column
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Good stuff Denis. This got me more than a few times 
Yes, here is a recent question for this problem
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.