What 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