sql - Get 0 value from a count with no rows

Question

I have SELECT:

SELECT c FROM (
    SELECT "candidate_id" as id, count("candidate_id") as c
    FROM "Applicaions"
    GROUP BY "candidate_id"
) as s WHERE id= _SOME_ID_;

But this only returns a value if count > 0. If count = 0 it returns nothing. How can I get 0 for a "Candidate" that doesn't have any application?

There is table "Candidates".
I need to get 0 if candidate has no applications or does not exist.

EDIT

I have now:

SELECT COALESCE ((SELECT count("candidate_id") as c
FROM "Applicaions" WHERE "candidate_id"=_SOME_ID_
GROUP BY "candidate_id"), 0);

It works perfectly. But is it possible to write it simpler or is this the best solution? Should I create any indexes?

Answer 1

You need to use the COALESCE function in PostgreSQL http://developer.postgresql.org/pgdocs/postgres/functions-conditional.html

Essentially you need to tell SQL how to handle NULLs. i.e. When NULL return 0.