>>>I'm looking for a formula. Assuming I have a rate of 1, 2, 3, 4, and 5. I then have 2 results for 3 and 2 results for 5. This would then give 4.00 as the rating result I am looking for. I'm looking for the formula which would give that rating.
>>
>>select sum(Rate*Num) as TotalSum, sum(Num) as TotalNum from yourtable into cursor curResult
>>
>>RatingResult=curResult.TotalSum/curResult.TotalNum
>>
>>Fourth update :)
>
>That is good as long as I have a table which would include 5 records with the total rates for each of them. In my case, I have a table which holds each individual rate, as we use that to verify for the user not to submit again, so the other solution proposed will work.
Now it's even simpler:
select sum(Rate) as TotalRate, count(*) as TotalNum from yourtable into cursor curResult
Then apply the formula, I already showed :)
If it's not broken, fix it until it is.
My Blog