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!

What is your RDBMS (SQL flavor)? – PM 77-1 May 13 '13 at 18:28 — PM 77-1, May 13 '13 at 18:28

score 46 · Accepted Answer · answered May 13 '13 at 18:30

46

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

answered May 13 '13 at 18:30

PM 77-1

12,933
21
68
111

13

Is there a way to do this if the table has many columns and I don't want to explicitly write them all out? – Burrito Feb 25 '22 at 20:20

score 12 · Answer 2 · answered May 24 '16 at 07:03

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

This answer is great, though be aware that `COLUMN_NAME` isn't quoted, so would be improved by substituting `QUOTENAME(COLUMN_NAME)` to support column names with spaces, reserved words etc. (Would make the change myself but the edit queue is full) — Joe Lee-Moyet, Feb 15 '22 at 11:55

score 9 · Answer 3 · answered May 13 '13 at 18:29

9

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

answered May 13 '13 at 18:29

Taryn

242,637
56
362
405

1

@Matthew Not necessary, NULL sums as 0. – Joachim Isaksson May 13 '13 at 18:35
@JoachimIsaksson really? 1+NULL = 0? I guess I haven't checked in a while. – Matthew May 13 '13 at 18:41
Thanks! Funny I had typed that out and was going to try that when I got back to work, but I figured I'd still ask. If I would have known about the site you did the Demo on I could have tested it myself. Thanks! – Anonymoose May 13 '13 at 19:12

score 4 · Answer 4 · answered May 26 '16 at 15:54

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

answered May 26 '16 at 15:54

Daniel Barral

3,896
2
35
47

score 3 · Answer 5 · edited Sep 21 '20 at 09:41

3

select
    sum(case when a is null then 1 else 0 end) as a_null_count,
    sum(case when b is null then 1 else 0 end) as b_null_count,
    sum(case when c is null then 1 else 0 end) as c_null_count
from table

edited Sep 21 '20 at 09:41

Soumendra Mishra

3,483
1
12
38

answered May 13 '13 at 18:30

dmansfield

1,108
10
22

score 0 · Answer 6 · answered Jul 11 '21 at 19:10

I am very late to this, but if you don't want to manually list out all the column names and still want to get a table, you can do this in SQL Server (Just replace testTable with your actual table):

--Creates the table the poster wanted

CREATE TABLE testTable (A int, B int, C int)
INSERT INTO testTable (A, C) VALUES (1,1)
INSERT INTO testTable (A, B) VALUES (1,1)
INSERT INTO testTable (B, C) VALUES (1,1)
INSERT INTO testTable (C) VALUES (1)

--Creates the output table which will consist of each column name and the amount of nulls

CREATE TABLE ColumnNames (
    ID int IDENTITY(1,1) PRIMARY KEY,
    [name] varchar(max),
    nullAmount int
)

INSERT INTO ColumnNames ([name]) 
SELECT [name] FROM sys.columns WHERE object_id = OBJECT_ID('dbo.testTable')

DECLARE @columnIndex INT = 1

WHILE @columnIndex <= ( SELECT COUNT(*) FROM dbo.ColumnNames )
BEGIN
    DECLARE @colName nvarchar(max) = (SELECT [name] FROM ColumnNames WHERE ID = @columnIndex)
    EXEC('SELECT ' + @colName + ' INTO colTable FROM testTable')

    DECLARE @SQL nvarchar(max) = N'UPDATE ColumnNames SET nullAmount = (SELECT COUNT(1) - COUNT(' + quotename(@colName) + ') FROM colTable) WHERE ID = @columnIndex'
    EXEC SP_EXECUTESQL @SQL, N'@columnIndex int', @columnIndex 

    DROP TABLE colTable
    SET @columnIndex = @columnIndex + 1
END

--Select the output table and get null info
SELECT * FROM ColumnNames

Count NULL Values from multiple columns with SQL

6 Answers6

Linked