Oracle: How to count null and non-null rows

Question

I have a table with two columns that might be null (as well as some other columns). I would like to count how many rows that have column a, b, both and neither columns set to null.

Is this possible with Oracle in one query? Or would I have to create one query for each? Can't use group by or some other stuff I might not know about for example?

Formic

Answer 1

COUNT(expr) will count the number of rows where expr is not null, thus you can count the number of nulls with expressions like these:

SELECT count(a) nb_a_not_null,
       count(b) nb_b_not_null,
       count(*) - count(a) nb_a_null,
       count(*) - count(b) nb_b_null,
       count(case when a is not null and b is not null then 1 end)nb_a_b_not_null
       count(case when a is null and b is null then 1 end) nb_a_and_b_null
  FROM my_table

Yount

Answer 2

Something like this:

SELECT sum(case 
               when a is null and b is null then 1
               else 0
           end) as both_null_count,
       sum(case
               when a is null and b is not null then 1
               else 0
           end) as only_a_is_null_count
FROM your_table

You can extend that for other combinations of null/not null

Chasse

Answer 3

select sum(decode(a,null,0,1)) as "NotNullCount", sum(decode(a,null,1,0)) as "NullCount"
from myTable;

Repeat for as many fields as you like.

Dynamometry

Answer 4

It can be accomplished in Oracle just in 1 row:

SELECT COUNT(NVL(potential_null_column, 0)) FROM table;

Function NVL checks if first argument is null and treats it as value from second argument.

Rubella

doesn't answer question ("How to count null and non-null rows") – Archdiocese

Answer 5

This worked well for me for counting getting the total count for blank cells on a group of columns in a table in oracle: I added the trim to count empty spaces as null

SELECT (sum(case 
           when trim(a) is null Then 1
           else 0
       end)) +
   (sum(case
           when trim(b) is null 
           else 0
       end)) +
   (sum(case
           when trim(c) is null 
           else 0
       end)) as NullCount
FROM your_table

Hope this helps

Cheers.

Bede

Answer 6

SQL>CREATE TABLE SAMPLE_TAB (COL1 NUMBER NOT NULL, COL2 DATE DEFAULT SYSDATE, COL3 VARCHAR2(20));
SQL>INSERT INTO SAMPLE_TAB(COL1,COL2,COL3) VALUES(121,SYSDATE-2,'SAMPLE DATA');
SQL>INSERT INTO SAMPLE_TAB(COL1,COL2,COL3) VALUES(122,NULL,NULL); --ASSIGN NULL TO COL2
SQL>INSERT INTO SAMPLE_TAB(COL1,COL3) VALUES(123,'SAMPLE DATA RECORD 3');--COL2 DEFAULT VALUE ASSIGN AS SYSDDATE AS PER STRUCTURE.
SQL>COMMIT;
SQL> SELECT * FROM SAMPLE_TAB;

SQL> SELECT *
FROM USER_TAB_COLUMNS U
WHERE 1=1
AND TABLE_NAME='SAMPLE_TAB'
AND NUM_NULLS!=0;

SQL> ANALYZE TABLE SAMPLE_TAB COMPUTE STATISTICS;


SQL> SELECT *
FROM USER_TAB_COLUMNS U
WHERE 1=1
AND TABLE_NAME='SAMPLE_TAB'
AND NUM_NULLS!=0;

Bisector

Answer 7

One way to do it would be:

select count(*) from table group by nvl2(a, 0, 1), nvl2(b, 0, 1) having nvl2(a,0,1) = nvl2(b,0,1);

Alleris