MySQL: How Can I Combine Multiple Conditional AVG Values With Group By

I have a site that lets users rate videos based on different tags such as how funny or how interesting they think a video is, allowing users to rate the same video in multiple ways.

The site currently allows sorting videos by specific tags which takes into account the average user rating of the tag for that video. I am now trying to make a MySQL query that can sort videos based on the average aggregate ratings of multiple tags. For example, showing all videos that are both funny and interesting.

I can't figure out how to do this with one query.

This is what to use to sort by one tag

SELECT *, AVG(ratings.rating) as avgTagFunny 
FROM videos, ratings 
WHERE videos.id = ratings.video_id 
AND ratings.tag_id = 2 
GROUP BY video_id
ORDER BY avgTagFunny DESC

The closest I got was doing something like this

SELECT *, AVG(ratings.rating) as avgTag, count(distinct tag_id) as distinctTags
FROM videos, ratings 
WHERE videos.id = ratings.video_id 
AND ratings.tag_id IN (2, 12) 
GROUP BY video_id 
HAVING distinctTags > 1
ORDER BY avgTag DESC

The problem with this is that it takes the average of all ratings so if 3 users rated how funny a video is and only one rated how interesting it is, the overall average is skewed. What I want is to sort by the average rating of one tag plus the average of another tag divided by 2.

Here's some sample data, as requested.

Tables:

videos: id, title
tags: id, name
users: id, name
ratings: id, user_id, video_id, tag_id, rating

Example data for all ratings of video id 342:

1, 7, 342, 2, 90
2, 10, 342, 2, 80
3, 10, 342, 12, 70
4, 11, 342, 2, 85
5, 7, 342, 12, 50

As you can see, average ratings of tag 2 would be 85 and average ratings of tag 12 would be 60. Average of those two together is 72.5. We don't want the average of all ratings because that would be 75 and skewed towards one tag because there are more of tag 2 than of tag 12.

The first query I posted orders by average ratings of one tag. The second query I posted orders them by all ratings of both tags, but isn't ideal because it's skewed.

I'm looking for a way to order the results by (average ratings of tag 2 + average ratings of tag 12) / 2. I hope that makes sense!

Solution

SELECT  video_id, AVG(rating) rating
FROM    (
        SELECT  video_id, AVG(rating) rating
        FROM    ratings
        WHERE   tag_id IN (2, 12)
        GROUP BY
                video_id, tag_id
        ) q
GROUP BY
        video_id
ORDER BY
        rating DESC