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.

Answer 2

You can't.

If your candidate has no applications, then you have no way to read their candidate_id value

How would you have knowledge that a candidate exists without them being in the Applications table?

In your example code, there is no candidate and therfore you couldn't possible say that a specific candidate had zero applications. By that logic there are an infinite number of candidates having zero applications.

You will need a table of candidates in order to derive this information... unless your intention is to presume a candidate exists because you're asking for it by ID?

EDIT

Now that you have a Candidates table you can do this:

SELECT c.ID, (SELECT COUNT(a.*) FROM Applications a WHERE a.candidate_id = c.ID)  FROM Candidate c WHERE ....