Home > DeveloperSection > Forums > How to Calculate an average number from three columns?
Samuel Fernandes
Samuel Fernandes

Total Post:154

Points:1082
Posted on    May-25-2015 7:38 AM

 MSSQL Server SQL Server 2008 
Ratings:


 1 Reply(s)
 333  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

(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?



lois waisbrooker
lois waisbrooker

Total Post:70

Points:494
Posted on    May-25-2015 8:22 AM

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.


Don't want to miss updates? Please click the below button!

Follow MindStick