Get 0 Value From A Count With No Rows

July 02, 2024 Post a Comment

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

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 NULLs. 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, (SELECTCOUNT(a.*) FROM Applications a WHERE a.candidate_id = c.ID) 
FROM Candidate c
WHERE ....

Solution 3:

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:

SELECTcoalesce((SELECTcount(candidate_id)
FROM   "Applications" WHERE candidate_id = _SOME_ID_), 0) AS c;

The WHERE condition limits to a single candidate_id and there is a single aggregate function in the SELECT 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:

SELECTcount(a.candidate_id) AS c
FROM  (SELECT _SOME_ID_ AS candidate_id) x
LEFTJOIN "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
LEFTJOIN "Applicaions" a USING (candidate_id)
GROUPBY x.candidate_id;

LEFT JOIN is typically faster for a long list than multiple WHERE clauses or an IN expression.

Or, for all rows in your table "Candidates":

SELECT x.candidate_id, count(a.candidate_id) AS c
FROM   "Candidates" x
LEFTJOIN "Applications" a USING (candidate_id)
GROUPBY 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:

SELECTCASE c WHEN NULL THEN0ELSE c ENDFROM (
    SELECT"candidate_id"as id, count("candidate_id") as c
    FROM"Applicaions"GROUPBY"candidate_id"
) as s WHERE id= _SOME_ID_;

assuming that the 'nothing' is really NULL

Solution 5:

Can't you use this statement:

SELECT count("candidate_id") as c
FROM"Applicaions"WHERE"candidate_id"=_SOME_ID_
GROUPBY"candidate_id"

It should return count() and you don't need subquery.

EDIT: Matthew PK is correct and Andy Paton has better answer ;)

Lab Sql