Average of multiple columns

Question

I have a table called Request and the data looks like:

Req_ID    R1   R2   R3   R4   R5  R12673    2    5    3    7    10 R34721    3    5    2    1    8 R27835    1    3    8    5    6 

Now I want to display the average of R1,R2,R3,R4 and R5

So I wrote a query like:

Select Req_ID, Avg(R1+R2+R3+R4+R5) as Average from Request Group by Req_ID 

But I just get the sum of R1,R2,R3,R4 and R5 not the average? Where am I doing wrong.

Answer 1

You don't mention if the columns are nullable. If they are and you want the same semantics that the AVG aggregate provides you can do (2008)

SELECT *,        (SELECT AVG(c)         FROM   (VALUES(R1),                       (R2),                       (R3),                       (R4),                       (R5)) T (c)) AS [Average] FROM   Request   

The 2005 version is a bit more tedious

SELECT *,        (SELECT AVG(c)         FROM   (SELECT R1                 UNION ALL                 SELECT R2                 UNION ALL                 SELECT R3                 UNION ALL                 SELECT R4                 UNION ALL                 SELECT R5) T (c)) AS [Average] FROM   Request