Let's assume you have very simple table with users:

# \d users
                           Table "public.users"
  Column   |  Type   |                     Modifiers
-----------+---------+----------------------------------------------------
 id        | integer | not null default nextval('users_id_seq'::regclass)
 username  | text    | not null
 is_active | boolean | not null default true
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_username_key" UNIQUE, btree (username)

And you'd like to count all users, and know how many of them are active …

Well, you could do 2 queries:

SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE is_active = TRUE;

Or, do it this way:

SELECT is_active, COUNT(*) FROM users GROUP BY is_active;

This would give you 2 rows of data, and you would have to manually add the values to get total count.

This is a bit inconvenient, but it is faster than previous approach, as it runs only 1 table scan.

Is there any way to get both values in 1 table scan? Yes.

We can use the fact that count() counts only not null values.

So, if we could somehow change inactive users into NULLs – we could do it.

There is simple way with CASE:

SELECT CASE WHEN is_active = TRUE THEN TRUE ELSE NULL END;

But it is just too long. Luckily, there is another function we could use: nullif().

Using this function I can write the query like this:

SELECT COUNT(*) AS ALL, COUNT(NULLIF(is_active, FALSE)) AS active FROM users;

Simple and effective. Of course – we should check if it is faster or slower than “group by" approach.

So, I tested it.

Group by, ran 10 times gave me average time of 2510.9ms.

Double-count+nullif approach, ran on the same machine, also 10 times, gave me average time of 2440ms.

Difference is small, so of course there is no point in saying “this way is faster". But – it is not slower.