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?
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:
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.