query errors when all data not present I have a working query that show success rate of staff.  When I run it in for all staff it works flawlessly.
However, when I insert it as a function within a java based page I get an error:

ERROR: division by zero


Here is the query and what I think the actual problem is:

SELECT st.staff_id, 
  round((count(s.code IN ('10401','10402','10403') OR NULL) * 100.0) / count(*), 1) AS successes
  FROM   notes n
  JOIN   services s  ON s.zzud_service = n.zrud_service
  WHERE n.zrud_staff = ? AND n.date_service &gt;= DATE '07/01/2013' 
    AND n.date_service &lt;= CURRENT_DATE 
    AND  s.code IN ('10401','10402','10403','10405')
  GROUP  BY st.staff_id


The 10401, 10402, 10403 codes reflect successful discharges.
The 10405 reflect unsuccessful discharges.

If a staff has no notes (new staff for example) I get the division by zero error.

So I believe if a staff does not have at least one entry for one of the codes, it will error.  This prevents them from logging in.

I read of an operator called NULLIF() that would prevent this, but I do not have any idea how to insert it, and, if this will actually work.

Can anyone comment?
count(*)


should become

coalesce(count(*), 1)


for the simplest case. The denominator doesn't actually matter because 0/anything is always zero.



Also:

s.code IN ('10401','10402','10403') OR NULL


is nonsense. count(x OR NULL) is exactly the same as count(x). NULL is false in boolean context, x OR NULL is the same as just saying x. So just write:

s.code IN ('10401','10402','10403')


If what you're actually trying to say is "count rows that are either one of the listed values or are null" then you need a coalesce. You can't just use IN (..., NULL) since x IN (..., NULL) is always either true or NULL, since x IN (...) is equivalent to x = ANY (...) and x = NULL is NULL. So you must write:

s.code IN ('10401','10402','10403') OR s.code IS NULL