XML Feeds
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
