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
tsql
- 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
tsql
- 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
tsql
- 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.