Getting average from 3 columns in SQL Server

Question

I have a table with 3 columns (smallint) in SQL Server 2005.

Table Ratings
ratin1 smallint,
ratin2 smallint
ratin3 smallint

These columns can have values from 0 to 5.

How can I select the average value of these fields, but only compare fields where the value is greater then 0.

So if the column values are 1, 3 ,5 - the average has to be 3. if the values are 0, 3, 5 - The average has to be 4.

Solution 1

This is kind of quick and dirty, but it will work...

SELECT (ratin1 + ratin2 + ratin3) / 
((CASE WHEN ratin1 = 0 THEN 0 ELSE 1 END) + 
(CASE WHEN ratin2 = 0 THEN 0 ELSE 1 END) + 
(CASE WHEN ratin3 = 0 THEN 0 ELSE 1 END) +
(CASE WHEN ratin1 = 0 AND ratin2 = 0 AND ratin3 = 0 THEN 1 ELSE 0 END) AS Average

Solution 2

@mwigdahl - this breaks if any of the values are NULL. Use the NVL (value, default) to avoid this:

Sum columns with null values in oracle

Edit: This only works in Oracle. In TSQL, try encapsulating each field with an ISNULL() statement.

Comments

[mwigdahl]

There is, but the questioner wants to average across columns, not across rows.

[mwigdahl]

I really don't think this is what the questioner wants. As I read it, he wants to average ratin1, ratin2, and ratin3 within the context of a single row.

[Neil Knight]

If that is the case, then they can just amend the first SELECT statement to say SELECT AVG(col1 + col2 + col3)

[and_the_rand]

AVG(col1 + col2 + col3) is the sum of col1, col2, and col3 for single rows

[barbarian]

What about fields with zero value, I wouldn't like to include it in average, see my question.

[Jhonny D. Cano -Leftware-]

when the three are 0 you would get division by zero, isn't it?

[Jhonny D. Cano -Leftware-]

+ (CASE WHEN ratin 1 = 0 AND ratin2 = 0 AND ratin3 = 0 THEN 1 ELSE 0 END)