Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle: How to count null and non-null rows

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?

like image 241
Svish Avatar asked Apr 01 '11 11:04

Svish


People also ask

Does COUNT include NULL in Oracle?

For multi-assign attributes, the COUNT function counts all non-NULL sets in the group. Note that because sets are never NULL but can be empty, COUNT will also count a record with an empty set (that is, an empty set is returned for any record that does not have an assignment for the specified multi-assign attribute).

Does COUNT () ignores NULL?

COUNT(expression) does not count NULL values.

Which command is used to COUNT the number of rows and non-NULL values in Oracle database?

5. Which of the following commands is used to count the number of rows and non-NULL values in Oracle database? Answer: D. The COUNT (ALL column_name) is used to count number of rows excluding NULLs.


2 Answers

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 
like image 138
Vincent Malgrat Avatar answered Oct 11 '22 14:10

Vincent Malgrat


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

like image 22
a_horse_with_no_name Avatar answered Oct 11 '22 13:10

a_horse_with_no_name