Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Irrelevant to the question: The OR NULL should be removed. –  ypercube Dec 8 '13 at 8:21

1 Answer 1

up vote 1 down vote accepted
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
share|improve this answer
    
When I add coalesce, it errors at the "AS" statement, so I figured I needed another ending paran : round((count(s.code IN ('10401','10402','10403') OR NULL) * 100.0) / count(*), 1) AS successes –  Detox Dec 8 '13 at 19:23
    
When I add coalesce, it errors at the "AS" statement, so I figured I needed another ending paran : round((count(s.code IN ('10401','10402','10403') OR NULL) * 100.0) / count(*), 1) AS successes This fixes the "AS" error, but the same result occurs = if there is not at least one of the codes present, the query fails. I tried omitting the "OR NULL" and again, if there is not at least one of the codes present it fails. –  Detox Dec 8 '13 at 20:21
    
@Detox Your comments appear to completely ignore the contents of this answer. Where's the coalesce around the count to protect against a null denominator? The whole statement suggests you need to study how NULL works in SQL, it doesn't behave consistently or how you probably think it does. –  Craig Ringer Dec 8 '13 at 23:42
    
Please forgive me if I have disrespected you! My abbreviated responses did not reflect what actually happened. When I use the line SELECT st.staff_id, round((count(s.code IN ('10401','10402','10403') OR NULL) * 100.0) / count(*), 1) AS successes as it stands, it results in success rate of 50% for a staff who logs in with one service of 10401 and 10405 this gives a code from both sides. If a staff logs in with a single code of 10405, it shows success rate of 0%. If a staff logs in with none of the codes present, it errors. I took to heart your comment and read a lot on your suggestion –  Detox Dec 9 '13 at 14:49
    
If I remove the "OR NULL" from the end of this -SELECT st.staff_id, round((count(s.code IN ('10401','10402','10403') OR NULL) * 100.0) it skews the result to 100% or nothing. I then ammended it to SELECT st.staff_id, round((count(s.code IN ('10401','10402','10403') OR NULL) * 100.0) / NULLIF (count(*),0)) AS success and that gives accurate percentage if at least one code is present and a blank % if there are no codes present. I will experiment a few more times to make sure it is not a cruel cosmic joke that it is working and will let you know. Thanks for all your help –  Detox Dec 9 '13 at 15:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.