Daily Archives: Dec 26, 2014


Count of total not null values from all columns of a table 1

In this post, we will count not null values from all the columns of a given table. One of my friend asked me to get the count of all not null values from all the columns of a given table. I used a CASE statement in my query and achieved this task but again i thought can i do this with the help of COUNT function too? And then i found an another way to achieve this task too. Here i am sharing both the ways. I am also sharing a trick to find only NULL values too.
To demonstrate this first create a table and put some demo data as below;

Create a Table:

CREATE TABLE dbo.TEST
(
    ColA VARCHAR(10),
    ColB VARCHAR(10),
    ColC VARCHAR(10),
    ColD VARCHAR(10),
    ColE VARCHAR(10),
    ColF VARCHAR(10)
)
GO

Add rows:

INSERT INTO dbo.TEST(ColA, ColB, ColC, ColD, ColE, ColF)
VALUES('Mango', 'Apple', 'Banana', NULL, NULL, NULL),
('Mango', 
More