Does anyone have some quick and dirty (copyright jimpen
) T-SQL to return a set with a single row that contains a count of all rows where a specific value is NULL (say column_name), and a count of the rows in the entire table (say table_name)?
I've put together the below CTE, which works fine, but it seems like there's a more elegant way to do this that I'm not grasping.
Thanks in advance.
WITH m as (
SELECT 'Account' as label, COUNT(id) as row_count_column_name_missing
WHERE column_name IS NULL)
, a as (
SELECT 'Account' as label, COUNT(id) as row_count_all
CAST(m.row_count_column_name_missing / CAST(row_count_all as numeric(19,4)) * 100 as numeric(5,2)) as pct_missing
JOIN a ON m.label = a.label