---
title: "How to Calculate an average number from three columns?"  
description: "How to Calculate an average number from three columns?"  
author: "Samuel Fernandes"  
published: 2015-05-25  
updated: 2015-05-25  
canonical: https://www.mindstick.com/forum/23260/how-to-calculate-an-average-number-from-three-columns  
category: "mssql server"  
tags: ["sql server 2008"]  
reading_time: 2 minutes  

---

# How to Calculate an average number from three columns?

I am try to calculate an average number from three columns but only includes the [column](https://www.mindstick.com/forum/33860/how-to-calculate-column-summary-in-sql-server) in the [calculation](https://www.mindstick.com/forum/160723/factorial-calculation) if column is [not null](https://www.mindstick.com/interview/1933/what-is-not-null-constraint) 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](https://answers.mindstick.com/qa/48468/who-wrote-the-the-wrong-enemy-america-in-afghanistan-2001-2014-and-when) 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?

## Replies

### Reply by Anonymous User

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](https://www.mindstick.com/forum/33922/how-to-use-null-coalescing-operator-in-c-sharp) if () returns NULL if no rating meets the conditions. And, the > 0 is is not true for NULL values.


---

Original Source: https://www.mindstick.com/forum/23260/how-to-calculate-an-average-number-from-three-columns

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.