How to avoid dividing by zero in SQL query? [duplicate]

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.

Answer 1

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"

Answer 2

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"

Answer 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";