Re: How do I do this query?

[Quoted] On Tuesday, February 6, 2018 at 7:59:08 PM UTC+5:30, Tony Mountifield wrote:
> In article,
> wrote:
> > I have a "teachers" table with fields for primarykey, name, and collegeid (for the college that they teach in). I have a
> > separate teacherreviews table (cause 1 teacher can have multiple reviews) which has teacherid (the primarykey from the
> > teachers table) and rating fields.
> > Now I want to know the average rating of ALL teachers from a particular college - ie. if the History teacher at that
> > college is rated 5, and the Geography teacher 7, then the average will be 6 for that college. How do I do this? The only
> > thing I can think of is to do a SELECT DISTINCT on the college field, but....my poor brain is burning out at that point!
> > :( How do I do the "all colleges with the same id" bit? I want to order the colleges by the highest no. of teacher
> > ratings.
>
> You need to JOIN the teachers table to the teacherreviews tablei (ON the teacherid field), and then GROUP BY collegeid,
> selecting AVG(rating) for each collegeid. You can also return COUNT(*) as the number of ratings for that college, and
> use an ORDER BY to sort on the count of ratings.
>
> I could give you the whole query, but it does sound a bit like a homework assignment, so it would be better if you
> work it out from the above hints!
>
> Cheers
> Tony
>
> --
> Tony Mountifield
>
> - http://tony.mountifield.org

No no this is not homework, and there's no need to give me the whole query, but..how do you do the "avg rating *for each* collegeid"?? I just looked up GROUP BY in my book, I *think* I get it, but um.... I'm still stuck at that avg rating...hehe :) It's the avg for EACH collegeid part that I'm not getting.... Received on Wed Feb 07 2018 - 06:23:23 CET