SUM () does not work in MySQL: SUM () with DISTINCT

Question

I have 4 tables called stores, users, review and rating.

I want to get all the reviews for the corresponding store with detailed information about the user, as well as the overall rating for this store.

I did with almost one request. But the problem is that the store has the same rating several times by the same user, it is considered a single rating. But this rating was correct.

i.e

from this table user_id 3 was rated shop_id 1 as 4 times. Thus, the counter is 4, and total_rating is 17.

My request

select review.comments, users.username, count(distinct rating.id) as rating_count, sum(distinct rating.rating) as total_rating from users left join review on users.id = review.user_id and review.shop_id='1' left join rating on users.id = rating.user_id and rating.shop_id='1' where review.shop_id='1' or rating.shop_id='1' group by users.id, review.user_id, rating.user_id, review.id 

When I run this request, I received

But I need total_rating 17 for user_id 3 ..

Tick fiddle

Answer 1

You put DISTINCT IN sum( rating.rating) as total_rating, so the result is ( 12 = 17-5 ), since it will include 5 only once when calculating the sum.

  select review.comments, review.user_id, count(distinct rating.id) as rating_count, sum( rating.rating) as total_rating from users left join review on users.id = review.user_id and review.shop_id='1' left join rating on users.id = rating.user_id and rating.shop_id='1' where review.shop_id='1' or rating.shop_id='1' group by users.id, review.user_id, rating.user_id, review.id 

Here is the SQLFiddle

Sampling Result: Hope this helps

Answer 2

Try this: remove differences from sum(rating.rating) . Since you gave sum(distinct rating.rating) , it ignores one 5 that user 3 gave to save 1.

 select review.comments, users.username, count(distinct rating.id) as rating_count, sum(rating.rating) as total_rating from users left join review on users.id = review.user_id and review.shop_id='1' left join rating on users.id = rating.user_id and rating.shop_id='1' where review.shop_id='1' or rating.shop_id='1' group by users.id, review.user_id, rating.user_id, review.id 

Answer 3

First of all: it makes no sense to write external records from the table, and then delete them in the WHERE clause. Using left join review ... you will say: find the corresponding table in the table overview, and if you do not find it, add zeros so that we save the user record. Then with where review.shop_id='1' you say: keep only the entries in which you actually found the entry in the overview. Thus, you are firing notes that you simply mistook for pain. Your WHERE clause makes your LEFT EXTERNAL CONNECTIONS simply INPUT CONNECTIONS.

As for your actual problem: this is due to the first join of all the tables and only then an attempt to get aggregates from the resulting records. Assembly before connection:

 select rev.comments, usr.username, coalesce(rat.rating_count, 0) as rating_count, rat.total_rating from review rev join users usr on users.id = review.user_id left join ( select user_id, shop_id, count(*) as rating_count, sum(rating) as total_rating from rating group by user_id, shop_id ) rat on rat.user_id = usr.id and rat.shop_id = rev.shop_id where rev.shop_id = 1 group by rev.id;