Counting null and non-null values in a single query

This works for Oracle and SQL Server (you might be able to get it to work on another RDBMS):

select sum(case when a is null then 1 else 0 end) count_nulls
     , count(a) count_not_nulls 
  from us;

Or:

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

If I understood correctly you want to count all NULL and all NOT NULL in a column...

If that is correct:

SELECT count(*) FROM us WHERE a IS NULL 
UNION ALL
SELECT count(*) FROM us WHERE a IS NOT NULL

Edited to have the full query, after reading the comments :]

SELECT COUNT(*), 'null_tally' AS narrative 
  FROM us 
 WHERE a IS NULL 
UNION
SELECT COUNT(*), 'not_null_tally' AS narrative 
  FROM us 
 WHERE a IS NOT NULL;

Here is a quick and dirty version that works on Oracle :

select sum(case a when null then 1 else 0) "Null values",
       sum(case a when null then 0 else 1) "Non-null values"
from us

for non nulls

select count(a)
from us

for nulls

select count(*)
from us

minus 

select count(a)
from us

Hence

SELECT COUNT(A) NOT_NULLS
FROM US

UNION

SELECT COUNT(*) - COUNT(A) NULLS
FROM US

ought to do the job

Better in that the column titles come out correct.

SELECT COUNT(A) NOT_NULL, COUNT(*) - COUNT(A) NULLS
FROM US

In some testing on my system, it costs a full table scan.

Just to provide yet another alternative, Postgres 9.4+ allows applying a FILTER to aggregates:

SELECT
  COUNT(*) FILTER (WHERE a IS NULL) count_nulls,
  COUNT(*) FILTER (WHERE a IS NOT NULL) count_not_nulls
FROM us;

SQLFiddle: http://sqlfiddle.com/#!17/80a24/5

Try this..

SELECT CASE 
         WHEN a IS NULL THEN 'Null' 
         ELSE 'Not Null' 
       END a, 
       Count(1) 
FROM   us 
GROUP  BY CASE 
            WHEN a IS NULL THEN 'Null' 
            ELSE 'Not Null' 
          END 

If you're using MS Sql Server...

SELECT COUNT(0) AS 'Null_ColumnA_Records',
(
    SELECT COUNT(0)
    FROM your_table
    WHERE ColumnA IS NOT NULL
) AS 'NOT_Null_ColumnA_Records'
FROM your_table
WHERE ColumnA IS NULL;

I don't recomend you doing this... but here you have it (in the same table as result)

All the answers are either wrong or extremely out of date.

The simple and correct way of doing this query is using COUNT_IF function.

SELECT
  COUNT_IF(a IS NULL) AS nulls,
  COUNT_IF(a IS NOT NULL) AS not_nulls
FROM
  us

SELECT
    ALL_VALUES
    ,COUNT(ALL_VALUES)
FROM(
        SELECT 
        NVL2(A,'NOT NULL','NULL') AS ALL_VALUES 
        ,NVL(A,0)
        FROM US
)
GROUP BY ALL_VALUES

if its mysql, you can try something like this.

select 
   (select count(*) from TABLENAME WHERE a = 'null') as total_null, 
   (select count(*) from TABLENAME WHERE a != 'null') as total_not_null
FROM TABLENAME

I created the table in postgres 10 and both of the following worked:

select count(*) from us

and

select count(a is null) from us

In my case I wanted the "null distribution" amongst multiple columns:

SELECT
       (CASE WHEN a IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS a_null,
       (CASE WHEN b IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS b_null,
       (CASE WHEN c IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS c_null,
       ...
       count(*)
FROM us
GROUP BY 1, 2, 3,...
ORDER BY 1, 2, 3,...

As per the '...' it is easily extendable to more columns, as many as needed

Number of elements where a is null:

select count(a) from us where a is null;

Number of elements where a is not null:

select count(a) from us where a is not null;