How to Calculate an average number from three columns?

Total Post:159

 921  View(s)
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


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,
 ((imdbrating + metacritic + tomato.rating)/3) as average
FROM movie, tomato
WHERE movie.imdbid = tomato.imdbid

How can I implement this?

  1. Post:70

    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,
           ((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.

      Modified On Apr-09-2018 11:25:45 PM



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