Re: How do I do this query?
Date: Wed, 7 Feb 2018 07:07:44 +0100
Message-ID: <fdvjdgFpb1vU1_at_mid.individual.net>
On 02/07/18 06:23, bit-naughty_at_hotmail.com wrote:
> 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.... >
When you GROUP BY, then the AVG() will give you the average for those rows grouped together. To get trustworthy values when grouping things together, those you group with should be the only columns you select out over those columns where you make mathematical calumniations like AVG().
say you have a table with id, name, street, town, country, num_cars, num_bikes
then if you group by street and town, then your select can't have country nor name
select street, town, avg(num_cars) as avg_cars, count(*) as
how_many_lives_on_that_street
from table peple_with_cars_and_bikes
group by street, town
If you want to include country, then you add that to the columns selected and group by.
I know mysql may be a bit more forgiving than other databases with group by, but I recommend you keep to this rule, you never know when you suddenly have to work with another database.
-- //AhoReceived on Wed Feb 07 2018 - 07:07:44 CET