Re: How do I do this query?

In article <220f74c7-8d66-4368-a7d9-354c9ae290b7_at_googlegroups.com>,  <bit-naughty_at_hotmail.com> 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
Work: tony_at_softins.co.uk - http://www.softins.co.uk
Play: tony_at_mountifield.org - http://tony.mountifield.org