I'm trying to retrieve 2 Averages from records stored in a table of "Business Reviews". The businesses are reviewed in several categories such as "service," "cleanliness," etc... The scores are based on a scale between 1 - 5. The reviews are then compiled based on category into a table with columns structured as:
business_id, question_category, R1, R2, R3, R4, R5
R1-R5 of course being how many people gave the business a score of 1, or 2, or 3...
I first need to total and average the business's scores per category. I then need to see what percentage of that category score was made up by people rating them at the highest possible level of "5"
My query works, but I'm sure there's a cleaner more efficient way. Perhaps a derived table or a self-join would be of use.
Here's my code:
Sorry for the long winded, convoluted question.
select business_id, category_id, avg(r1 + r2 + r3 + r4 + r5) as total_avg,
(sum(r5) / (sum(r1) + sum(r2) + sum(r3) + sum(r4) + sum(r5)) * 100) as top_score_avg
group by business_id, category_id