Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cidr
Posting Yak Master
207 Posts |
 Posted - 2011-04-27 : 06:47:11
|
Hi there,For some reason this is more complicated than I thought. I'm trying to average fields from multiple columns as shown in the diagram below. when I use the average function:AVG(Fields!Col1.value + Fields!Col2.Value + Fields!Col3.Value)I would get 9.5 for Row1 etc; it's summing the column instead. Please can anyone help me with this or point me in the right direction? Would I need to use VB for this?Thanks column 1 column2 column3 AverageRow1 3 3 3.5 3.17Row2 2 2.5 2.5 2.33Row3 4 2 2 2.67 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-27 : 06:59:52
|
When you use aggregate functions (such as AVG, SUM), they operate on a column - i.e., they aggregate on the column. There is no equivalent aggregation across columns. So you would need to do it the old-fashioned way as in(isnull(column1,0) + isnull(column2,0) + isnull(colum3,0))/3.0 If you need a column that has the average value calculated this way, you can even make that column a computed column. |
 |
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2011-04-27 : 08:43:12
|
| Hi and thanks for the help. I've used this before but I don't know why I thought I wasn't getting the right results with it!:) |
|
|
|
|
|
|
Average multiple columns