SUM() not working in MySQL : SUM() with DISTINCT

Question

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

I want to get all reviews for the corresponding shop with reviewed user details and also overall rating for that shop.

I have done almost with the single query. But the problem is if the shop has same rating for multiple times by same user its consider as single rating. But that rating count was correct.

i.e

from this table user_id 3 was rated shop_id 1 as 4 times. So the count is 4 and total_rating is 17.

My query is

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 query I got

But I need total_rating 17 for user_id 3..

Check this fiddle

Answer 1

You put DISTINCT IN sum( rating.rating) as total_rating, thats why the result(12=17-5), since it will include 5 only once while computing 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 SQLFiddle

Sample Output : Hope this helps

Answer 2

Try this - Remove the distinct from sum(rating.rating). Since you gave sum(distinct rating.rating), it is ignoring one 5 that user 3 gave to store 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 outer-join records from a table and then remove them in the WHERE clause. With left join review ... you say: find a matching record in table review, and if you don't find any, then add nulls, so we keep the users record. Then with where review.shop_id='1' you say: keep only records where you actually found a record in review. So you are dismissing the records that you just took the pain to keep. Your WHERE clause renders your LEFT OUTER JOINS mere INNER JOINS.

As to your actual problem: That stems from joining all tables first and only then trying to get aggregates from the resulting records. Aggregate before joining instead:

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;