Sometimes you want to know what the percentage is of null values in a table for a Column
Or you might want to know what the percentage of all values in a Column is grouped by value
You can get these answers by running the code below

First create this 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

Get the percentage of nulls in all the Columns in my table

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

Get the values and the percentage of all values for a Column

T-SQL
1
2
3
4
5
SELECT Column3 AS Value,COUNT(*) AS ValueCount,
100.0 * COUNT(coalesce(Column3,0))/(SELECT COUNT(*) FROM #perc ) AS Percentage
FROM #perc
GROUP BY Column3
ORDER BY Percentage DESC
SELECT Column3 AS Value,COUNT(*) AS ValueCount,
100.0 * COUNT(coalesce(Column3,0))/(SELECT COUNT(*) FROM #perc ) AS Percentage
FROM #perc
GROUP BY Column3
ORDER BY Percentage DESC

Output

Value	ValueCount	Percentage
1	6	66.666666666666
NULL	2	22.222222222222
2	1	11.111111111111