Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get 0 value from a count with no rows

Tags:

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?

like image 615
Miko Kronn Avatar asked Feb 01 '11 17:02

Miko Kronn


People also ask

How do you show zero as count if there is no record in database?

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.

Does count () Count Zero?

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.

Does Count () ignores NULL?

COUNT(expression) does not count NULL values.

Which function can be used to count no of rows in a table?

The SQL COUNT( ) function is used to return the number of rows in a table. It is used with the Select( ) statement.


2 Answers

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.

like image 115
diagonalbatman Avatar answered Sep 16 '22 15:09

diagonalbatman


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 .... 
like image 44
Matthew Avatar answered Sep 18 '22 15:09

Matthew