dictionaries: id (INT) | title (VARCHAR) | created_at

dictionary_ratings: id (INT) | dictionary_id (INT) | rating (TINYINT)

terms: id (INT) | dictionary_id (INT)

I have three SQL tables as shown above (simplified). I want to query all dictionaries and add information about the number of terms per each, as well as the calculated rating of each dictionary.

SELECT dictionaries.title,
       SUM(dictionary_ratings.rating) AS rating_count,
       COUNT(terms.id) AS term_count
FROM dictionaries
LEFT JOIN dictionary_ratings
    ON dictionaries.id = dictionary_ratings.dictionary_id
LEFT JOIN terms
    ON dictionaries.id = terms.dictionary_id
GROUP BY dictionaries.id
ORDER BY dictionaries.created_at DESC

The query above works OK except that it multiplies SUM(dictionary_ratings.rating) by COUNT(terms.id). So if I have 5 terms associated with a dictionary, and the total rating is 10, it outputs 50 instead of 10.

How do I fix that?

CodePudding user response：

You need to modify your query for get your expected result and for that your final query looks like :

SELECT 
  dictionaries.title, 
  sub.total_rating AS rating_count, 
  COUNT(terms.id) AS term_count 
FROM 
  dictionaries 
  LEFT JOIN (
    SELECT 
      dictionary_id, 
      SUM(rating) AS total_rating 
    FROM 
      dictionary_ratings 
    GROUP BY 
      dictionary_id
  ) sub ON dictionaries.id = sub.dictionary_id 
  LEFT JOIN terms ON dictionaries.id = terms.dictionary_id 
GROUP BY 
  dictionaries.id 
ORDER BY 
  dictionaries.created_at DESC

1. Here the first join is between dictionaries table and the result of a subquery, which calculates the total rating for each dictionary by summing the ratings for that dictionary in the dictionary_ratings table and grouping them by dictionary_id.

2. The second join is between the dictionaries table and the terms table.

3. And finally, the query then groups the results by the dictionaries.id field and orders the results by dictionaries.created_at field in desc order.