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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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
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

T-SQL
1
SUM(CASE WHEN ColumnName IS NULL THEN 1 ELSE 0 END) / COUNT(*)
SUM(CASE WHEN ColumnName IS NULL THEN 1 ELSE 0 END) / COUNT(*)

Run this query below

T-SQL
1
2
3
4
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
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

T-SQL
1
(COUNT(*) - COUNT(ColumnName)) / COUNT(*)
(COUNT(*) - COUNT(ColumnName)) / COUNT(*)

The query below will return the same output as the query above

T-SQL
1
2
3
4
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
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

T-SQL
1
select Column3 from #perc
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

T-SQL
1
2
3
4
5
6
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
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