CODEWITHSUNDEEP
mysql
Nicholas Finch
Nicholas Finch

Reputation: 327

MYSQL - Querying averages of ratings for multiple users in a single query

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions