Oracle: How to count null and non-null rows

oracle select null grouping

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

oracle select null grouping

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_countFROM your_table

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

oracle select null grouping

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.

CodeHunter

Oracle: How to count null and non-null rows

Recent Posts

How can I color dots in a xy scatterplot according to column value?

How to update a claim in ASP.NET Identity?

What does {0} mean when initializing an object?

Accessing members of items in a JSONArray with Java

How to log SQL statements in Spring Boot?

Powershell Get-WebSite name parameter is ignored

How to detect scroll to bottom of html element

Java synchronized method

How to test controllers with CodeIgniter?

Detect Visual Composer

Matplotlib: Specify format of floats for tick labels

Rails join a list of strings with commas and "and" before the last