Re: How do I do this query?

From: Tony Mountifield <tony_at_mountifield.org>
Date: Wed, 7 Feb 2018 10:22:02 +0000 (UTC)
Message-ID: <p5ek0a$kdq$1_at_softins.softins.co.uk>

In article <b6974a19-1d17-407a-97aa-20f1c6c43257_at_googlegroups.com>,  <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....

In article <a2fe5555-5746-431c-b566-d073ec9f1603_at_googlegroups.com>,  <bit-naughty_at_hotmail.com> wrote:
> Sorry, J O Aho, I'll read your post in a bit, but....I omitted 2 things in my first post:

>

> The teachers table has a "subject" field (for the subject they teach), and the website will let people query this field.
> and...
> the teacherreviews table can have multiple ratings (lines in the DB) FOR ONE TEACHER - *first*, all the ratings for a
> *particular teacher* will have to be averaged to get their "score" or whatever you wanna call it, and then all the
> teachers *of a particular college* will have each of THOSE scores averaged, to get the "overall teacher rating for a
> PARTICULAR college". How would I do this?
> Kind of makes my head spin...... :) (I'm guessing IN will be used...?)

>
>

> I was starting out with something like this:

>

> SELECT college ... FROM teachers, teacherreviews WHERE subject = <inputsubject> AND teachers.primarykey =
> teacherreviews.teacherid GROUP BY college ....

>

> The dots mean I don't know what to put in there :( And I also didn't do the averaging of 1 particular teacher from
> multiple ratings in the teacherreviews table either.... (actually I didn't do either average...)
>
> Where do I go from here? :)

OK, if it's not homework, I'll give you a bit more detail.

One point to make first, regarding "SELECT college ... FROM teachers, teacherreviews WHERE...". I would recommend NOT to use implicit joins (tables separated by comma with the join condition in the WHERE). It is much better and clearer to use explicit INNER JOIN or LEFT JOIN syntax, with the join condition in an ON clause. Then the overall WHERE condition only specifies conditions for selecting rows from the driving table or discarding rows from the result.

Also, your subjects should be in a separate table, not within the teachers table. Otherwise, what would happen if a teacher taught two or more subjects? It also makes sure subject spelling is standardised. You can read from the subjects table to create a <select> element for a drop-down list of subjects in your search form. You display the subject name within the <option>, but return subject_id as the value of the option.

You then need a linking table linking teacher_id with subject_id with an entry for each subject a teacher teaches.

So, something like this:

CREATE TABLE subjects (
  subject_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,   subject_name VARCHAR(80)
);

CREATE TABLE teacher (

  teacher_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  college_id INT UNSIGNED NOT NULL,
  teacher_name VARCHAR(80),

  ...other info...
);

CREATE TABLE teachsubj (
  teacher_id INT UNSIGNED NOT NULL,
  subject_id INT UNSIGNED NOT NULL,
  PRIMARY KEY(teacher_id, subject_id),
  UNIQUE KEY(subject_id, teacher_id),
);

CREATE TABLE colleges (
  college_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,   ... college details ...
);

CREATE TABLE teacherreviews (
  teacherreview_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,   teacher_id INT UNSIGNED NOT NULL,
  rating INT UNSIGNED NOT NULL
  .. other fields, such as date or source of the rating, comments, etc... );

So, to get the average rating per teacher:

SELECT teacher_id, AVG(rating) AS avgrating, COUNT(*) AS numratings FROM teacherreviews
GROUP BY teacher_id;

You can put that in a sub-select if you want other information about the teacher:

SELECT teacher_id, college_id, teacher_name, avgrating, numratings FROM (
  SELECT teacher_id, AVG(rating) AS avgrating, COUNT(*) AS numratings   FROM teacherreviews
  GROUP BY teacher_id
) ratings
INNER JOIN teachers ON teachers.teacher_id = ratings.teacher_id;

If you want to limit by subject, you can do that in the inner sub-select:

SELECT teacherreviews.teacher_id, AVG(rating) AS avgrating, COUNT(*) AS numratings FROM teacherreviews
INNER JOIN teachsubj ON teachsubj.teacher_id = teacherreviews.teacher_id AND teachsubj.subject_id=<selected subject id> GROUP BY teacherreviews.teacher_id

The INNER JOIN will ignore any teacherreviews records that don't have a matching teachsubj record.

If you then want to average the teacher scores per college, you have to do a GROUP BY college_id on the bigger SELECT, omitting the teacher-specific items:

SELECT college_id, AVG(avgrating) AS collegeavgrating, SUM(numratings) AS collegenumratings FROM (
  SELECT teacherreviews.teacher_id, AVG(rating) AS avgrating, COUNT(*) AS numratings   FROM teacherreviews
  INNER JOIN teachsubj ON teachsubj.teacher_id = teacherreviews.teacher_id AND teachsubj.subject_id=<selected subject id>   GROUP BY teacherreviews.teacher_id
) ratings
INNER JOIN teachers ON teachers.teacher_id = ratings.teacher_id GROUP BY college_id;

If you want to include college information too, then you put that whole query above inside a FROM(...) college and then do an INNER JOIN colleges ON colleges.college_id=college.college_id

Hope this helps you! None of this is tested, so there may be errors (but I've tried to avoid them!).

Cheers
Tony

-- 
Tony Mountifield
Work: tony_at_softins.co.uk - http://www.softins.co.uk
Play: tony_at_mountifield.org - http://tony.mountifield.org

Received on Wed Feb 07 2018 - 11:22:02 CET