CODEWITHSUNDEEP
mysqlsql
Michael
Michael

Reputation: 4390

What is wrong with this SQL

I have the following SQL query:

SELECT tblBooks.bookID,
    tblBooks.title,
    tblBooks.author,
    tblBooks.coverImage,
    ROUND(ROUND(tblReviews.rating * 2) / 2, 1) AS rating
FROM tblBooks
    LEFT JOIN tblReviews
        ON tblBooks.bookID = tblReviews.bookID
        LEFT JOIN tblMembers
            ON tblReviews.userID = tblMembers.userID
WHERE tblReviews.bookID IS NOT NULL
ORDER BY rating DESC
LIMIT 0, 40

I would like to query the database and return the average rating, so if a book has been reviewed 6 times, total up each rating from the 6 users and calculate the average. Right now only the latest rating is being returned. When I change this line to:

ROUND(ROUND(AVG(tblReviews.rating) * 2) / 2, 1) AS rating

only one result is returned in total so there is obviously something wrong but I have no idea what.

If anyone can shed any light on this, I think it will have something to do with my joins.

Upvotes: 1

Views: 99

Answers (5)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

You don't need the WHERE either. You are essentially doing an INNER join:

SELECT t.bookID,
       t.title,
       t.author,
       t.coverImage,
       AVG(r.rating) AS rating
FROM tblBooks AS t
    JOIN tblReviews AS r
        ON b.bookID = r.bookID
GROUP BY t.bookID
--     , t.title, t.author, t.coverImage 
ORDER BY rating DESC
LIMIT 40 ;

Note: Using only GROUP BY t.bookID (and no other columns there) will give correct results only if bookID is the primary key (or has a unique constraint) in tblBooks. All other DBMS do not allow this (except Postgres but that product has implemented the feature correctly and it does check if the other columns depend on the grouping columns). MySQL does no check at all, so it can give erroneous results in many cases.

It also depends on the sql_mode settings, whether the above will run without error.

You could alternatively, first group by and then join. This is valid SQL and no error results will be possible:

SELECT t.bookID,
       t.title,
       t.author,
       t.coverImage,
       g.rating
FROM tblBooks AS t
    JOIN 
        ( SELECT r.bookID,
                 AVG(r.rating) AS rating
          FROM tblReviews AS r
          GROUP BY r.bookID
        ) AS g
        ON b.bookID = r.bookID
ORDER BY rating DESC
LIMIT 40 ;

Upvotes: 1

What have you tried
What have you tried

Reputation: 11148

Why not use AVG instead?

SELECT tblBooks.bookID,
    tblBooks.title,
    tblBooks.author,
    tblBooks.coverImage,
    AVG(tblReviews.rating) AS rating
FROM tblBooks
    LEFT JOIN tblReviews
        ON tblBooks.bookID = tblReviews.bookID
        LEFT JOIN tblMembers
            ON tblReviews.userID = tblMembers.userID
WHERE tblReviews.bookID IS NOT NULL
Group By tblBooks.bookID, tblBooks.title, tblBooks.author, tblBooks.coverImage
ORDER BY rating DESC
LIMIT 0, 40

FYI

In MYSQL, you don't need to group by all columns all though it's a good practice because you will need to when using other RDMS.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You need to use a group by like this:

SELECT tblBooks.bookID, tblBooks.title, tblBooks.author, tblBooks.coverImage,
       AVG(tblReviews.rating) as avgRating
FROM tblBooks
     LEFT JOIN tblReviews
     ON tblBooks.bookID = tblReviews.bookID
WHERE tblReviews.bookID IS NOT NULL
group by tblBooks.bookID, tblBooks.title, tblBooks.author, tblBooks.coverImage
ORDER BY avgrating DESC

Your query doesn't seem to use any information from the Members table, so I eliminated it. It is not even filtering based on it, because you are using left join.

Upvotes: 3

d'alar'cop
d'alar'cop

Reputation: 2365

Try this:

...
WHERE tblReviews.bookID IS NOT NULL
GROUP BY tblBooks.bookID
ORDER BY rating DESC
LIMIT 0, 40

Cheers.

Upvotes: 0

RandomUs1r
RandomUs1r

Reputation: 4190

I think what you're missing is 

 GROUP BY tblBooks.bookID, tblBooks.title, tblBooks.author, etc...

AVG is an aggregate function that sums out data, use GROUP BY to create groups of aggregates.

Upvotes: 0

Related Questions