Get 0 value from a count with no rows
Solution 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 NULL
s. i.e. When NULL
return 0
.
Solution 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 ....
Solution 3
I was made aware of this question by the author of this related one who was led to believe his problem could not be solved after reading here. Well, it can.
This answer is almost two years old, but the final questions were still pending.
Query
Is it possible to write it simpler or is this the best solution?
To test for a single ID, the query you found is good. You can simplify:
SELECT coalesce((SELECT count(candidate_id)
FROM "Applications" WHERE candidate_id = _SOME_ID_), 0) AS c;
The
WHERE
condition limits to a singlecandidate_id
and there is a single aggregate function in theSELECT
list.GROUP BY candidate_id
was redundant.The column alias was swallowed by
COALESCE()
. If you want to name the resulting column move the alias to the end.- You don't need double quotes for a regular lower case identifier.
Another, cleaner (IMHO) form would be to use LEFT JOIN
:
SELECT count(a.candidate_id) AS c
FROM (SELECT _SOME_ID_ AS candidate_id) x
LEFT JOIN "Applicaions" a USING (candidate_id)
This works nicely for multiple IDs, too:
WITH x(candidate_id) AS (
VALUES
(123::bigint)
,(345)
,(789)
)
SELECT x.candidate_id, count(a.candidate_id) AS c
FROM x
LEFT JOIN "Applicaions" a USING (candidate_id)
GROUP BY x.candidate_id;
-
LEFT JOIN
is typically faster for a long list than multipleWHERE
clauses or anIN
expression.
Or, for all rows in your table "Candidates":
SELECT x.candidate_id, count(a.candidate_id) AS c
FROM "Candidates" x
LEFT JOIN "Applications" a USING (candidate_id)
GROUP BY x.candidate_id;
Indexes
Should I create any indexes?
If read performance is important and the table holds more than just a couple of rows you definitely need an index of the form:
CREATE INDEX foo_idx ON "Applications" (candidate_id);
Since this seems to be a foreign key column referencing "Candidates".candidate_id
, you should most probably have that to begin with.
Solution 4
Perhaps:
SELECT CASE c WHEN NULL THEN 0 ELSE c END
FROM (
SELECT "candidate_id" as id, count("candidate_id") as c
FROM "Applicaions"
GROUP BY "candidate_id"
) as s WHERE id= _SOME_ID_;
assuming that the 'nothing' is really NULL
Solution 5
You could try something like the following (assuming you have a candidate table and my assumption of table/column names are correct).
SELECT c FROM (
SELECT "Candidate"."candidate_id" as id,
count("Applications"."candidate_id") as c
FROM "Candidate" LEFT JOIN "Applications"
ON "Applications"."candidate_id"="Candidate"."id"
GROUP BY "Candidate"."candidate_id" ) as s WHERE id= _SOME_ID_;
Related videos on Youtube
Miko Kronn
Updated on July 09, 2022Comments
-
Miko Kronn almost 2 years
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
. Ifcount = 0
it returns nothing. How can I get0
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?
-
Matthew over 13 yearsYou need a
Candidate
table unless you just want to return your queried ID with a "zero found" results or something. -
Miko Kronn over 13 yearsThere is "Candidates" table. How can I get 0 for those that doesn't have applications?
-
Matthew over 13 yearsWithout using the
Candidates
table there are an infinite number of candidates without applications... 10001,10002,10003,...99999 ad infinitum
-
-
Miko Kronn over 13 yearsYeah. 'nothing' is NULL. Your answer is good, but now I like COALESCE :)
-
Miko Kronn over 13 yearsI know. I removed them. Just wanted to tell that I need 0 value, not '0' string for displaying. But removing 's is something I can figure out on my own as opposed to joins, triggers etc :P. I don't know SQL well :(