Miko Kronn Miko Kronn - 26 days ago 9
SQL Question

Get 0 value from a count with no rows

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

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.