mysql distinct sum,SUM（）在MySQL中不起作用：具有DISTINCT的SUM（）

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

解决方案

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

Sample Output :

Hope this helps