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

``````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

``````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

``````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
```