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.
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 can use ISNULL or COALESCE:both are same with a small difference. ISNULL(param1,param2): can contains only 2 parameter, and there are no condition of having it's value.
The result is a BIGINT value. It is an aggregate function, and so can be used with the GROUP BY clause. COUNT(*) counts the total number of rows in a table. COUNT() returns 0 if there were no matching rows.
COUNT(expression) does not count NULL values.
The SQL COUNT( ) function is used to return the number of rows in a table. It is used with the Select( ) statement.
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 NULL
s. i.e. When NULL
return 0
.
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 ....
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With