在表中的所有列中计数null和非空值

Question

我有一个带有两个列的表：

name         marks
  A            15
  B            
  c            20

从上面可以明显看出，列name具有3个非null值和1个空值，同样，marks的列有2个NULL和2个非null值。

获得此结果的查询是什么？

Answer 1

count(columnname)仅计数非编号值。count(*)计数所有行。

select count(name) as names,
       count(*) - count(name) as null_names,
       count(marks) as marks,
       count(*) - count(marks) as null_marks
from tablenam

Answer 2

您可以做到这一点 -

SELECT COUNT(name) AS name_not_null_count, 
       SUM(CASE WHEN name IS NULL THEN 1 ELSE 0 END) AS name_null_count
FROM table

计算null计数的方法是：用1标记所有空记录，并以SUM。

Answer 3

SELECT COUNT(name) AS name_count, COUNT(marks) AS marks_count FROM table;

Answer 4

这将选择数量的行数，无效的行数以及具有非null值的行数。重复每个所需列的选择。

 CREATE TABLE #tmp
 (
     i int
 )
 INSERT INTO #tmp values(1)
 INSERT INTO #tmp values(2)
 INSERT INTO #tmp values(3)
 INSERT INTO #tmp values(NULL)
 SELECT COUNT(*) AS Num_Rows
  , COUNT(i) AS Num_Not_Null
  , COUNT(*)-COUNT(i) AS Num_Null 
 FROM #tmp

num_rows num_not_null num_null

---

4 3 1