forum

Home / DeveloperSection / Forums / How to Calculate an average number from three columns?

Ask Question

How to Calculate an average number from three columns?

Samuel Fernandes 1569 25-May-2015

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?

mssql server sql server 2008 
Updated on 25-May-2015
Samuel Fernandes

Samuel Fernandes

Follow

Can you answer this question?

Answer

1 Answers

Anonymous User

Anonymous User

25-May-2015

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.

Liked By