What makes this difference when using group by or join?

Question

Can anybody tell the difference between them? The 1st and 3rd query can be successfully executed, and they have the same output. While the 2nd and 4th query cannot be executed, and they both raised an ERROR:

column "movie.title" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT title, (MAX(stars)-MIN(stars)) AS ratingspread.

My questions are:

why using m.mid are difference with r.mid while using group by (query 1 vs query 2)
why A inner join B is not equal to B inner join A (query 3 vs query 4)

    SELECT title, (MAX(stars)-MIN(stars)) AS ratingspread
    FROM rating r JOIN movie m
    ON r.mid = m.mid
    GROUP BY m.mid
    ORDER BY ratingspread DESC, title;

    SELECT title, (MAX(stars)-MIN(stars)) AS ratingspread
    FROM rating r JOIN movie m
    ON r.mid = m.mid
    GROUP BY r.mid
    ORDER BY ratingspread DESC, title;

    SELECT title, (MAX(stars) - MIN(stars)) AS ratingspread
    FROM movie
    INNER JOIN rating USING(mId)
    GROUP BY mId
    ORDER BY rating_spread DESC, title;

    SELECT title, (MAX(stars)-MIN(stars)) AS ratingspread
    FROM rating 
    INNER JOIN movie USING(mId)
    GROUP BY mId
    ORDER BY ratingspread DESC, title

FYI the schema goes like this:

Movie ( mID, title, year, director )There is a movie with ID number mID, a title, a release year, and a director.

Reviewer ( rID, name ) The reviewer with ID number rID has a certain name.

Rating ( rID, mID, stars, ratingDate ) The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate.

I think you need to do some reading on the purpose and use of [group by](https://www.geeksforgeeks.org/sql-group-by/). Put simply, as your error states, when grouping you either need to group by the column being selected, or you need to aggregate the column. — Dale K, Apr 13 '20 at 03:29
Does this answer your question? [must appear in the GROUP BY clause or be used in an aggregate function](https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function) — Dale K, Apr 13 '20 at 03:37
@DaleK Dale, Thank you for your help. After digging into some group by examples. Everything got cleared. — Shan Jiang, Apr 14 '20 at 01:47

score 0 · Answer 1 · answered Apr 13 '20 at 04:09

Your queries are not producing what you think they are producing for a number of reasons.

Here is what you are really looking for I think:

SELECT
    movie.mid
    movie.title, 
    (MAX(rating.stars)-MIN(rating.stars)) AS ratingspread
FROM
    movie
    INNER JOIN rating on movie.mid = rating.mid
GROUP BY
    movie.mid,
    title
ORDER BY
    (MAX(stars)-MIN(stars)) DESC,
    title

There are a few things to point out: Firstly, you need to join on the matching column - in some of your queries you are joining mid to rid - these are unrelated fields. The movie ID is what joins the rating to the movie. Secondly, you are not getting the GROUP BY concept. What you are trying to do is get the spread of ratings for a given movie and display its title, so to display its title (and any other non-summarised data), you have to include the field in the group by. For further illustration, imagine you wanted to get the spread of all reviews by each reviewer, to see if they had any bias towards going hard or going soft on the movies they were reviewing. Here is how you would get the spread of reviews for each reviewer:

SELECT
    reviewer.rid,
    reviewer.name, 
    (MAX(rating.stars)-MIN(rating.stars)) AS ratingspread
FROM
    reviewer
    INNER JOIN rating on reviewer.rid = rating.rid
GROUP BY
    reviewer.rid,
    reviewer.name
ORDER BY
    (MAX(stars)-MIN(stars)) DESC,
    reviewer.name

By the way, the reason you want to include the ID as well as the title or reviewer name is to ensure you eliminate problems where two movies share the same title, or two reviewers have the same name.

What makes this difference when using group by or join?

1 Answers1