CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SP1
SP1

Reputation: 1202

Calculate average per row and then calculate average per column

I have a table shown below

Col1    Col2    Col3    Col4
NULL    NULL    NULL    54.84
NULL    NULL    NULL    75.40
57.24   73.61   NULL    NULL

I want to first calculate the average of the row and then calculate the final average of the column

So my calculation becomes 

    Row1Avg   54.84
    Row2Avg   75.40
    Row3Avg   65.42

And then I arrive at the final average of Row1Avg,Row2Avg,Row3Avg = 65.22

Can some please tell me how to achieve this in an efficient manner. How I was thinking of doing it is I have a temp table with single column where I store the AVG of the rows and then I just take average of the temp table column. Was just wondering if there might be a better way of doing it.

Upvotes: 1

Views: 1247

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would use cross apply and grouping sets:

select t.id, avg(row_avg)
from t cross apply
     (select avg(col) as row_avg
      from (values (col1), (col2), (col3), (col4)) v(col)
     ) s
group by grouping sets ( (id), () );

The above assumes that you have a unique id to identify each row. If not, then you need something like union all:

with s as (
      select t.*, s.row_avg
      from t cross apply
           (select avg(col) as row_avg
            from (values (col1), (col2), (col3), (col4)) v(col)
           ) s
     )
select s.row_avg
from s
union all
select avg(s.row_avg)
from s;

Upvotes: 1

arunes
arunes

Reputation: 3524

Try with CROSS APPLY

SELECT 
    AVG(t2.Average)
FROM
    Table t1
    CROSS APPLY (
        SELECT Average = AVG(Value) 
        FROM (VALUES (Col1), (Col2), (Col3), (Col4)) B1(Value)
    ) t2

Result is 65.221666

Upvotes: 1

Dylan
Dylan

Reputation: 11

So you need both average for each row and each column? I would do two separate calculations, one for row and another for column. Read through the table and find the average for each row, store the column values in a temp column tables or lists.

Upvotes: 0

Related Questions