Counting null and non-null values in a single query

Question

I have a table

create table us

(

 a number

);

Now I have data like:

a

1

2

3

4

null

null

null

8

9

Now I need a single query to count null and not null values in column a

Answer 1

The below code will work for Oracle and SQL Server:

select sum(case when a is null then 1 else 0 end) count_nulls

     , count(a) count_not_nulls 

  from us;

Otherwise, you can use this:

select count(*) - count(a), count(a) from us;