Подсчитайте комментарии и получите средний рейтинг от mysql

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

У меня есть 3 таблицы (активность, рейтинг, комментарии), активность содержит основные данные «активности», в рейтинге хранятся оценки и комментарии - конечно же, рейтинги.

activity_table

id | title |short_desc | long_desc | address | lat | long |last_updated

рейтинг_таблица

id | activityid | userid | rating

comment_table

id | activityid | userid | rating

Теперь я пытаюсь получить данные из активности плюс comment_counts и average_rating в одном запросе.

SELECT activity.*, AVG(rating.rating) as average_rating, count(comments.activityid) as total_comments
FROM activity LEFT JOIN
     rating
     ON activity.aid = rating.activityid LEFT JOIN
     comments
     ON activity.aid = comments.activityid
GROUP BY activity.aid 

... не работает. Это дает мне правильный средний_рейтинг, но неправильное количество комментариев.

Любые идеи? Большое спасибо!

См. meta.stackoverflow.com/questions/333952/…

Strawberry 31.10.2018 12:17
2
1
115
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вы агрегируете по двум разным параметрам. Декартово произведение, генерируемое объединениями, влияет на агрегирование.

Итак, вы должны агрегировать перед join:

SELECT a.*, r.average_rating, COALESCE(c.total_comments, 0) as total_comments
FROM activity a LEFT JOIN
     (SELECT r.activityid, AVG(r.rating) as average_rating
      FROM rating r
      GROUP BY r.activityid
     ) r
     ON a.aid = r.activityid LEFT JOIN
     (SELECT c.activityid, COUNT(*) as total_comments
      FROM comments c
      GROUP BY c.activityid
     ) c
     ON a.aid = c.activityid;

Обратите внимание, что внешний GROUP BY больше не нужен.

Другие вопросы по теме