如何计算SQL Server中每个值的丢失数据（零，空，空空间）？

Question

我有一个带有两个列Col1和col2的桌子。我正在尝试计算所有代码（Code1，code2，code3，code4 ...）的空空间或零列的百分比 对于下表：

代码	col1	col2
code1	null	0
代码	2 3	1
代码	2 3	1
代码2
代码	1 null	null
code1	4	1

i想要一个摘要表，如下：

codes	tocus_count_count_values	sissing_values	sissing_percent
code1	3	2	66.67
code2	3	1	33.33

我尝试了下面的代码，但在下面尝试了代码被卡住了

;WITH allcount AS(
SELECT COUNT(*) Total FROM (
SELECT ft.Codes, Description, col1, col2 FROM data1 d1
)A
),baddata AS(
SELECT f1.Code, COUNT(*) BadCount FROM (
SELECT f1.Codes, Description, col1, col2 FROM data1 d1
)B
WHERE (col1 IS NULL AND col2 IS NULL) OR (col1 = ' ' AND col2 = ' ') OR (col1 = '0' OR col2 = 
'0')
GROUP BY f1.ReadCode
)
SELECT ReadCode, Total AS total_count, BadCount as Missing_values,  100*(BadCount/Total) as 
Missing_Percent
FROM allcount, baddata

，我不确定如何将整个过程放在一起，请帮助我

Answer 1

似乎您只需要一些有条件的聚合：

SELECT Codes,
       COUNT(Codes) AS TotalCount,
       COUNT(CASE WHEN NULLIF(Col1,'') IS NULL OR NULLIF(Col2,'') IS NULL THEN 1 END) AS MissingValues,
       AVG(CASE WHEN NULLIF(Col1,'') IS NULL OR NULLIF(Col2,'') IS NULL THEN 1. ELSE 0. END) AS MissingPercent
FROM dbo.YourTable
GROUP BY Codes;