AVG across row

[kermitforney]

Anyway to calculate an average across a row, without using a cursor?

Ex.
col1, col2, col3, avg of columns
1, 2, 1, 1.33

Thanks,

 

[gmmastros]

It depends on your data.

The AVG function is pretty cool because it ignores nulls. If you have data in all your columns (no nulls) then it could be as simple as this:

Select Col1, Col2, Col3, (Col1 + Col2 + Col3) / 3.0 As Avg

If you do have nulls to deal with, then you could get in to messy case statements or something like this...

Select PKColumn, Avg(DataColumn)
From   (
       Select PKColumn, Col1 As DataColumn
       From   Table
       
       Union All 
       
       Select PKColumn, Col1
       From   Table
       
       Union All 
       
       Select PKColumn, Col1
       From   Table
       ) As AliasName
Group By AliasName.PKColumn

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kermitforney]

My issue is with dealing with "null" values. :/

 

[gmmastros]

The second example (in the code window) should properly accommodate nulls. I encourage you to try it and see if it returns the results you expect.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

the example I posted is wrong. Sorry.

Select PKColumn, Avg(DataColumn)
From   (
       Select PKColumn, [!]Col1[/!] As DataColumn
       From   Table
       
       Union All
       
       Select PKColumn, [!]Col2[/!]
       From   Table
       
       Union All
       
       Select PKColumn, [!]Col3[/!]
       From   Table
       ) As AliasName
Group By AliasName.PKColumn

Basically, this will take the data across columns and make them rows, then apply the avg function.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom