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!
SELECT COUNT(*)-COUNT(A) As A, COUNT(*)-COUNT(B) As B, COUNT(*)-COUNT(C) As C
FROM YourTable;
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
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)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
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
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
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
