Hello,

I have been around in circles with this and cannot figure this out. I am trying to get the weighted average on a sql report based on a count and an amount.

So first I get the average price of something we sell by creating an expression: Price/Count

(it will not let me do a calc field because I am using the count aggregate function)

We only sell 4 major items, which I am basing my report on. So I know that the next step is to take each of my item's average price, multiply it by the count for that item, add up those totals, then divide by the total count.

I have tried variations on this expression:

=(SUM((Fields!CRDTAMNT.Value/COUNT(Fields!ACCOUNT.Value))* COUNT(Fields!ACCOUNT.Value)))/COUNT(Fields!ACCOUNT.Value)

(Account is the count) and I keep getting errors about nested aggregate functions.

Does anyone know how to do this?

Thanks!

Calculating the result manually has no bearing on how this should be done in SQL, but if you really want to know, multiply each service fee by the count, add those up, then divide by the total count.

If you are unsure or don't know how to do this, that's fine, but let someone else step in. I do need to get this resolved.