Question: I have 3 tables setup (items, reviews, ratings). I want to count the total number of ratings and the total number of reviews for each item, then combine them for total number of interactions, then output the top items based on interactions.

I am struggling with the query a bit.

`SELECT item.id, count(reviews.review_id) AS total_reviews, count(ratings.rating_id) AS total_ratings, total_reviews+total_ratings AS total_interactions FROM items LEFT OUTER JOIN reviews ON reviews.item_id=item.id LEFT OUTER JOIN ratings ON rating.item_id=item.id GROUP BY item.id ORDER BY total_interactions DESC LIMIT 0,1`

Answer: 
```
SELECT  *, total_reviews + total_ratings AS total_interactions
FROM    (
        SELECT  id,
                (
                SELECT  COUNT(*)
                FROM    reviews
                WHERE   item_id = i.id
                ) AS total_reviews,
                (
                SELECT  COUNT(*)
                FROM    ratings
                WHERE   item_id = i.id
                ) AS total_ratings
        FROM    items i
        ) q
ORDER BY
        total_interactions DESC
LIMIT 1

```
I'll try to just fix your original query, instead of writing another one:

```
SELECT
    item.id,
    count(distinct reviews.review_id) AS total_reviews,
    count(distinct ratings.rating_id) AS total_ratings,
    count(distinct reviews.review_id) + count(distinct ratings.rating_id) AS total_interactions
FROM
    items
    LEFT OUTER JOIN reviews ON
        reviews.item_id = item.id
    LEFT OUTER JOIN ratings ON
        rating.item_id = item.id
GROUP BY
    item.id
ORDER BY
    total_interactions DESC
LIMIT 0, 1

```

The key here is `COUNT(DISTINCT field)`. BTW, probably you'll just need it for the `total_ratings` value - just try it and see what you'll get.