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

Question

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?

count(columnname) counts only non-null values. count(*) counts all rows. Now you do the math! — jarlh
– jarlh, Commented May 16, 2019 at 7:28
i want to count columnwise null and non null records present in a table — ovess
– ovess, Commented May 16, 2019 at 7:29

jarlh · Accepted Answer · 2019-05-16 07:37:59Z

1

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

answered May 16, 2019 at 7:37

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shantanu Sharma · Accepted Answer · 2019-05-16 07:46:12Z

0

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.

edited May 16, 2019 at 7:46

answered May 16, 2019 at 7:35

Shantanu Sharma

4,1491 gold badge22 silver badges44 bronze badges

Comments

Fahmi · Accepted Answer · 2019-05-16 07:37:52Z

0

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

edited May 16, 2019 at 7:37

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

answered May 16, 2019 at 7:36

Zaid Annas

853 bronze badges

Comments

SAS · Accepted Answer · 2019-05-16 07:55:12Z

0

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

edited May 16, 2019 at 7:55

answered May 16, 2019 at 7:45

SAS

4,0852 gold badges33 silver badges49 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related