calculating a weighted average in SQL, SSRS
Posted on 2010-11-14
Does anyone know how to calculate a weighted average in SQL?
First I get the average of something: COUNT(transactions)/Fee = Average
Then to get the weighted average I would have to total the above values of 'Average' for the three different transactions types we use, and then divide that by the total count.
I cannot create a calculated field because in SSRS you can't use aggregate functions in a calc field. Also, in SSRS, I have tried creating an expression:
and I keep getting errors about nested aggregate functions.
So first of all, should I do this in my query or somewhere in SSRS?
Second of all, what is the code/syntax to achieve this?