Miko Kronn Miko Kronn - 3 months ago 23
SQL Question

Get 0 value from a count with no rows

I have SELECT:

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
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.


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?


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.