Count NULL Values from multiple columns with SQL

Question

I have 3 columns let say A, B, and C. I need to count the NULL values in each column.

For example:

 A  | B  | C
-------------
1   |NULL| 1
1   | 1  | NULL
NULL| 1  | 1
NULL|NULL| 1

Should output:

 A  |  B  |  C
---------------
 2  |  2  |  1

I've tried count, sum, sub-queries but nothing has worked for me yet. Any input would be appreciated!

Answer 1

SELECT COUNT(*)-COUNT(A) As A, COUNT(*)-COUNT(B) As B, COUNT(*)-COUNT(C) As C
FROM YourTable; 

Answer 2

For SQL SERVER you can use the following:

SET NOCOUNT ON
DECLARE @Schema NVARCHAR(100) = '<Your Schema>'
DECLARE @Table NVARCHAR(100) = '<Your Table>'
DECLARE @sql NVARCHAR(MAX) =''
IF OBJECT_ID ('tempdb..#Nulls') IS NOT NULL DROP TABLE #Nulls

CREATE TABLE #Nulls (TableName sysname, ColumnName sysname , ColumnPosition int ,NullCount int , NonNullCount int)

 SELECT @sql += 'SELECT  '''+TABLE_NAME+''' AS TableName , '''+COLUMN_NAME+''' AS ColumnName,  '''+CONVERT(VARCHAR(5),ORDINAL_POSITION)+''' AS ColumnPosition, SUM(CASE WHEN '+COLUMN_NAME+' IS NULL THEN 1 ELSE 0 END) CountNulls , COUNT(' +COLUMN_NAME+') CountnonNulls FROM '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+';'+ CHAR(10)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = @Schema
 AND TABLE_NAME = @Table

 INSERT INTO #Nulls 
 EXEC sp_executesql @sql

 SELECT * 
 FROM #Nulls

 DROP TABLE #Nulls

you will receive a result set with the count of Null values and non null values in each column of you table

Answer 3

You can use an aggregate function with a CASE expression:

select 
  sum(case when a is null then 1 else 0 end) A,
  sum(case when b is null then 1 else 0 end) B,
  sum(case when c is null then 1 else 0 end) C
from yt

See Demo

Answer 4

SELECT
(SELECT count(*) FROM my_table WHERE A is NULL) as A,
(SELECT count(*) FROM my_table WHERE B is NULL) as B,
(SELECT count(*) FROM my_table WHERE C is NULL) as C