How to avoid dividing by zero in SQL query?

Question

In my websites authors can upload their books. I display statistics on each page and one of the stats I display is the average rating of the author's books. I take the sum of all the ratings, and divide it by the number of books:

select 
    sum(BookRate)/count(BookID) as AvrageRate 
from Books 
where Author = "Some Author"

The problems appear when the user has not yet uploaded any books and an obvious error message is returned:

Divide by zero error encountered.

Is there a way to avoid this situation within the SQL query or do I have to first check the book count with one query, and then run another query for the stats?

Thanks world.

That can't happen with the actual query in your question. If no books are uploaded and the `where` clause returns no rows the `SUM(BookRate)` will be `NULL` and that trumps the divide by zero. — Martin Smith, Dec 24 '13 at 19:53

score 5 · Accepted Answer · answered Dec 24 '13 at 19:33

5

You can use CASE to cater for the sum of zero:

select case sum (BookID) 
    when 0 then 0
    else sum (BookRate)/sum (BookID) 
end as AvrageRate 
from Books where Author="Some Author"

BTW, don't you want to divide by the count, rather than sum to get the average?:

select case count(BookID) 
    when 0 then 0
    else sum (BookRate)/count(BookID) 
end as AvrageRate 
from Books where Author="Some Author"

answered Dec 24 '13 at 19:33

Szymon

42,577
16
96
114

Dividing by the count.. it was a typo. but yeah, it would make more sense :) – Michael Seltenreich Dec 24 '13 at 19:39
2

All answers work. you were the first. thanks! – Michael Seltenreich Dec 24 '13 at 19:50

score 3 · Answer 2 · answered Dec 24 '13 at 19:32

3

You can use NULLIF() if you are using Oracle

NULLIF( 0, 0 )

In SQL Server also you can try this:

Select dividend / nullif(divisor, 0)

So in your case it could be like this:

select sum (BookRate)/nullif(sum (BookID),0) as AvrageRate 
from Books where Author="Some Author"

answered Dec 24 '13 at 19:32

Rahul Tripathi

168,305
31
280
331

1

You can use this as well if you are using MS SQL – Verakso Feb 01 '16 at 11:50

Gordon Linoff · Answer 3 · 2013-12-24T19:58:13.063

3

If the user has not uploaded any books, then I'm not convinced that you will get a divide-by-zero error. Instead, no rows will be returned.

In any case, the safest way is to use the case statement:

select (case when sum(BookId) > 0 then sum(BookRate)/count(BookID) end) as AvrageRate
from Books
where Author="Some Author";

Or, for this particular case, to use the avg() aggregation function:

select avg(BookRate) as AvrageRate
from Books
where Author="Some Author";

edited Dec 24 '13 at 19:58

answered Dec 24 '13 at 19:34

Gordon Linoff

1,242,037
58
646
786

is using the avg function more efficient? – Michael Seltenreich Dec 24 '13 at 19:39
@MichaelSeltenreich . . . It is much clearer in intent, if that is what you want to do. As for efficiency, it should be about the same. – Gordon Linoff Dec 24 '13 at 19:44

How to avoid dividing by zero in SQL query?

3 Answers3