# 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
```

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

Now this table has data like below;

There are total 14 not null values in all the columns and 22 NULL values in all columns. And below we are trying to get this count in two different approaches.

## Approach 1 : Using CASE statement

To count NOT NULL values only

It’s so simple and the first one which comes in our mind;

```SELECT SUM(CASE WHEN ColA IS NULL THEN 0 ELSE 1 END
+ CASE WHEN ColB IS NULL THEN 0 ELSE 1 END
+ CASE WHEN ColC IS NULL THEN 0 ELSE 1 END
+ CASE WHEN ColD IS NULL THEN 0 ELSE 1 END
+ CASE WHEN ColE IS NULL THEN 0 ELSE 1 END
+ CASE WHEN ColF IS NULL THEN 0 ELSE 1 END) AS TotalNotNullCount
FROM dbo.TEST
```

#### Output:

14

To count NULL values only

In case you want to get the count of only NULL values, just reverse the logic inside CASE statement asCASE WHEN ColA IS NOT NULL THEN 0 ELSE 1 END.

## Approach 2 : Using COUNT Function

To count NOT NULL values only:

Hence COUNT function eliminates NULL values as any aggregate function do, we can also try this in a simple way to achieve this like below.

```SELECT COUNT(ColA) + COUNT(ColB) + COUNT(ColC) + COUNT(ColD) + COUNT(ColE) + COUNT(ColF) AS TotalNotNullCount FROM dbo.TEST
```

14

### To count NULL values only

In case you want to get the count of all NULL values only, you can try this COUNT(*) – COUNT(ColA) instead of COUNT(ColA) i.e. just subtract the count of total NOT NULL values from count of total values. COUNT(*) counts all rows even it has NULL in all the columns.
So what you suggest? It may be you also have some more techniques in your pocket and if it is, please don’t hesitate and just put your trick below in comment section, it may help someone by adding one more trick to achieve this task.

Rate This
[Total: 0    Average: 0/5]