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 >= DATE '07/01/2013' AND n.date_service <= 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?