Oracle: How to count null and non-null rows

oracle select null grouping
Svish picture Svish · Apr 1, 2011 · Viewed 56.6k times · Source

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?

Answer

Vincent Malgrat picture Vincent Malgrat · Apr 1, 2011

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

Related questions

DATEDIFF function in Oracle

I need to use Oracle but DATEDIFF function doesn't work in Oracle DB. How to write the following code in Oracle? I saw some examples using INTERVAL or TRUNC. SELECT DATEDIFF ('2000-01-01','2000-01-02') AS …

Read More
If statement in select (ORACLE)

Hi I have simply select and works great: select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity, gcrs.Area_name, gcrs.sector_name, substr(gcrs.stream_…

Read More
How to use a table type in a SELECT FROM statement?

This question is more or less the same as this In the package header : Declared the following row type: TYPE exch_row IS RECORD( currency_cd VARCHAR2(9), exch_rt_eur NUMBER, exch_rt_usd NUMBER); And this table type: TYPE …

Read More