CODEWITHSUNDEEP
sqlcountnull
ovess
ovess

Reputation: 13

Count null and non-null values in all columns in a table

I have a table with two column like below:

name         marks
  A            15
  B            
  c            20

As is clear from above, column name has 3 non-null values and 1 null value, similarly column marks has 2 null as well 2 non-null values.

What's the query to get this result?

Upvotes: 1

Views: 4129

Answers (4)

SAS
SAS

Reputation: 4045

This will select number of rows, number of rows with null, and number of rows with non-null value. Repeat the select for each desired column.

 CREATE TABLE #tmp
 (
     i int
 )

 INSERT INTO #tmp values(1)
 INSERT INTO #tmp values(2)
 INSERT INTO #tmp values(3)
 INSERT INTO #tmp values(NULL)

 SELECT COUNT(*) AS Num_Rows
  , COUNT(i) AS Num_Not_Null
  , COUNT(*)-COUNT(i) AS Num_Null 
 FROM #tmp

Num_Rows Num_Not_Null Num_Null

4 3 1

Upvotes: 0

Shantanu Sharma
Shantanu Sharma

Reputation: 4099

You can do this - 

SELECT COUNT(name) AS name_not_null_count, 
       SUM(CASE WHEN name IS NULL THEN 1 ELSE 0 END) AS name_null_count
FROM table

Approach to calculate null count is: mark all the null records with 1 and take SUM.

Upvotes: 0

jarlh
jarlh

Reputation: 44795

count(columnname) counts only non-null values. count(*) counts all rows.

select count(name) as names,
       count(*) - count(name) as null_names,
       count(marks) as marks,
       count(*) - count(marks) as null_marks
from tablenam

Upvotes: 1

Zaid Annas
Zaid Annas

Reputation: 85

SELECT COUNT(name) AS name_count, COUNT(marks) AS marks_count FROM table;

Upvotes: 0

Related Questions