HOW TO CALCULATE AN AVERAGE NUMBER FROM THREE COLUMNS?

Samuel Fernandes

Total Post:159

Points:1117
Posted by  Samuel Fernandes
 652  View(s)
Ratings:
Rate this:

I am try to calculate an average number from three columns but only includes the column in the calculation if column is not null and is bigger than 0;

For example the average usually is

(column1+column2+column3)/3

But if column3 is null or 0 then it will be

(column1+column2+column3)/2 or (column1+column2)/2

I have this sol far but it is not complete. The average is wrong when one of the columns is 0

SELECT movie.title,

 movie.imdbrating,

 movie.metacritic,

 tomato.rating,

 ((imdbrating + metacritic + tomato.rating)/3) as average

FROM movie, tomato

WHERE movie.imdbid = tomato.imdbid

How can I implement this?

  1. lois waisbrooker

    Post:70

    Points:494
    Re: How to Calculate an average number from three columns?

    I'm fixing the rest of the query to use table aliases and proper join syntax. But the case statements are what you really need:

    SELECT m.title, m.imdbrating, m.metacritic,

           t.rating,

           ((case when imdbrating > 0 then imdbrating else 0 end) +

            (case when metacritic > 0 then metacritic else 0 end) +

            (case when t.rating > 0 then t.rating else 0 end) +

           ) / nullif(coalesce((imdbrating > 0), 0) + coalesce((metacritic > 0), 0) + coalesce((t.rating > 0), 0)), 0)

    FROM movie m JOIN

         tomato t

         ON m.imdbid = t.imdbid;

     

    The denominator is using a convenient MySQL extension where Booleans are treated as 0 or 1 in a numeric context. The null if () returns NULL if no rating meets the conditions. And, the > 0 is is not true for NULL values.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!