Menu
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?
241
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).
COUNT(expression) does not count NULL values.
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.
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 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
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
