#### How to Calculate an average number from three columns?

Total Post:159

Points:1117
1036  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 averageFROM movie, tomatoWHERE movie.imdbid = tomato.imdbid`

How can I implement this?

1. 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.

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

#### CONTACT INFO

10-B Taskand Marg, Near Patrika Chauraha, Civil Lines, Prayagraj, UP, India-211001.
contact@mindstick.com
91-532-2400505

969-G Edgewater Blvd,Suite
793 Foster City-94404, CA (USA)
+1-650-242-0133