Matrix aggregates

Question

Post reply

Matrix aggregates

Morgan-191629

Grasshopper

Points: 17
More actions
June 28, 2005 at 4:58 pm

#167312

I have a dynamic column group in a matrix. I have read (Lachev) that the only aggragate operation allowed on data in row and column groups is Summing.
Has anyone figured out how to Average data across a row?
Example
name A   B   C   D     ave
tom    3   5    7   1       4
bob    2   5    6   11      6

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Select name, A, B, C, D, (A + B + C + D) / 4.0 as Average from dbo.YourTable

Morgan-191629 Grasshopper Points: 17 More actions · Answer 2

Well, if I go that route, I'll have to rewrite the slightly complicated SP that generates A,B,C & D. This might be doable. I'll post an update later today.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Can you provide all the informations then??

If you post the original query then I might be able to provide more info.

Morgan-191629 Grasshopper Points: 17 More actions · Answer 4

I am still a little hesitant to post my SP code, not because of secrecy but because of complexity

Perhaps a better example to illustrate my problem is

Desired result:

| A | B | C | D | AVG |

tom | 3 | 5 | 7 | 1 | 4 |

bob | 2 | 5 | 6 | 11 | 6 |

What I have created so far (with little difficulty) is:

| A | B | C | D |

tom | 3 | 5 | 7 | 1 |

bob | 2 | 5 | 6 | 11 |

This a matrix built on a Stored Procedure that returns:

student name | assessor name | score |

tom | A | 1 |

tom | A | 2 |

tom | B | 2 |

tom | B | 1 |

tom | B | 2 |

etc.......................

My matrix contains aggregates (sums) of assessor scores. I would to have an aggregate (avg) of these aggregates.

Right now I am either looking at

a)amending my current Stored Procedure or

b)writing another Stored Procedure that calls the current one and aggregates the returned results.

I was hoping that I could get Reporting Services to do some of the work for me.

Suggestions welcome.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

If you don't want to rerun the query for performance, then you can always create a temp table to old the results.

Insert the results in the temp table.

Select the #temp table with the average this time, drop the temp table at the end of the proc.