SUM () не работает в MySQL: SUM () с DISTINCT

У меня есть 4 таблицы под названием магазины, пользователи, обзор и рейтинг.

Я хочу получить все отзывы для соответствующего магазина с подробными сведениями о пользователе, а также общий рейтинг для этого магазина.

Я сделал почти с одним запросом. Но проблема в том, что магазин имеет одинаковый рейтинг за несколько раз одним и тем же пользователем, его считают единым рейтингом. Но рейтинг был верным.

т.е.

введите описание изображения здесь

из этой таблицы user_id 3 был оценен shop_id 1 как 4 раза. Таким образом, счет равен 4, а total_rating – 17.

Мой запрос

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

Когда я запускаю этот запрос, я получил

введите описание изображения здесь

Но мне нужно total_rating 17 для user_id 3 ..

Проверьте эту скрипку

Вы sum( rating.rating) as total_rating, DISTINCT IN sum( rating.rating) as total_rating, вот почему результат ( 12 = 17-5 ), так как он будет включать 5 только один раз при вычислении суммы.

  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

Вот SQLFiddle

Результат выборки: введите описание изображения здесь Надеюсь это поможет

Попробуйте это – Удалить отличную от sum(rating.rating) . Поскольку вы дали sum(distinct rating.rating) , она игнорирует один 5, который пользователь 3 дал для хранения 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

Прежде всего: нет смысла записывать внешние записи из таблицы, а затем удалять их в предложении WHERE. С left join review ... вы говорите: найдите соответствующую запись в обзоре таблицы, а если вы ее не найдёте, добавьте нули, чтобы мы сохранили запись пользователей. Затем, where review.shop_id='1' вы говорите: сохраняйте только записи, где вы действительно нашли запись в обзоре. Таким образом, вы увольняете записи, которые вы просто принимали за боль. Ваша фраза WHERE делает ваши ЛЕВЫЕ ВНЕШНИЕ СОЕДИНЕНИЯ просто ВХОДНЫМИ СОЕДИНЯЯМИ.

Что касается вашей реальной проблемы: это связано с тем, что сначала присоединяется ко всем таблицам, а затем пытается получить агрегаты из результирующих записей. Агрегат перед присоединением:

 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;