CODEWITHSUNDEEP
sqlsql-serveraverage
Ragha Guru
Ragha Guru

Reputation: 13

Average from a same row - SQL server

I have a table which is having 5 columns. I need to find the average using SQL for every row (ignoring the 0 columns). Can I get some help.

COL1 COL2 COL3 COL4 COL5 AVERAGE
  1    2    3   4     5   3
  4    0    4   0     4   4
  3    0    0   0     9   6
  7    0    0   0     0   7

Sorry the table did not come clearly. I need the average to be calculated for each row for any non-zero values. For eg: the 5 cols had (4,0,4,0,4) I do not need the average as 12/5, I need the average as 12/3 =4. Basically divide it by the number of nonzero values. It is not always divided by 5

Upvotes: 0

Views: 317

Answers (2)

uzi
uzi

Reputation: 4146

Here is another option using CROSS APPLY. You must be carefull with precision when calculating average. For example, what is your expected average for values 1, 1, 2, 2, 3? AVG will return 1, but if you expect it to be 1.8, you should use floating point datatype before calculating average

with cte as (
    select * from
        (values
            (1, 2, 3, 4, 5)
            ,(4, 0, 4, 0, 4)
            ,(3, 0, 0, 0, 9)
            ,(7, 0, 0, 0, 0)
            ,(1, 1, 2, 2, 3)
        ) t(COL1, COL2, COL3, COL4, COL5)
)

select
    *
from
    cte
    cross apply (
        select 
            average = avg(val) --change to avg(val * 1.0) to get floating point numeric
        from 
            (values (COL1), (COL2), (COL3), (COL4), (COL5)) t(val) 
        where val > 0
    ) q

Upvotes: 1

e_i_pi
e_i_pi

Reputation: 4820

This is a little trickier than first glance. Without using pivot tables (which is another method, but probably overkill here), you can sum the columns and then divide by the number of non-zero columns. The trick is when every column is zero, you need to cater for that specifically otherwise you will get a divide by zero error:

SELECT
  COL1, COL2, COL3, COL4, COL5,
  CASE WHEN (COL1 = 0 AND COL2 = 0 AND COL3 = 0 AND COL4 = 0 AND COL5 = 0) 
    THEN 0
    ELSE 
        (COL1 + COL2 + COL3 + COL4 + COL5)
        / (
            CASE WHEN COL1 = 0 THEN 0 ELSE 1 END +
            CASE WHEN COL2 = 0 THEN 0 ELSE 1 END +
            CASE WHEN COL3 = 0 THEN 0 ELSE 1 END +
            CASE WHEN COL4 = 0 THEN 0 ELSE 1 END +
            CASE WHEN COL5 = 0 THEN 0 ELSE 1 END
        )
    END AS AVERAGE
FROM stats

SQLFiddle

Upvotes: 2

Related Questions