Reputation: 327
I am building a site one aspect of which involves teachers and rating teachers. Ratings are made by students, and when students are looking for teachers, among the criteria I'd like for them to be able to filter the teachers by include their average ratings.
Each rating by each student of each teacher puts the number of stars they rate the teacher into the database. I already have a query that can calculate the average stars for each teacher
SELECT AVG(star) AS Average FROM rating_table WHERE type = 'Member_review'
AND teacher_id = id_number
with id_number obviously being the user_id of the teacher.
When people are on the search page for all teachers, I'd love for them to be able to filter the teachers by rating, among other criteria. I can put this query in a foreach loop and have it run down each and every teacher id, but I suspect this will be a huge drag on the server - is there a way for me to put a string of comma-seperated ids into the query and have it find the average rating for each one? I did try this query and it did not work
SELECT AVG(star) AS Average FROM rating_table WHERE type = 'Member_review'
AND teacher_id IN(1,2,3, etc...)
Is there a way in which i can get the query to take the average for each teacher user_id and have it out put something like this?
Average Teacher ID
3.5 6
4.6 2
Upvotes: 0
Views: 183
Reputation: 1271241
Yes. This is a basic aggregation query:
SELECT AVG(star) AS Average, teacher_id
FROM rating_table
WHERE type = 'Member_review' and
teacher_id IN(1,2,3, etc...)
group by teacher_id
Upvotes: 2