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

i have site lets users rate videos based on different tags such how funny or how interesting think video is, allowing users rate same video in multiple ways.

the site allows sorting videos specific tags takes account average user rating of tag video. trying make mysql query can sort videos based on average aggregate ratings of multiple tags. example, showing videos both funny , interesting.

i can't figure out how 1 query.

this use sort 1 tag

select *, avg(ratings.rating) avgtagfunny  videos, ratings  videos.id = ratings.video_id  , ratings.tag_id = 2  group video_id order avgtagfunny desc 

the closest got doing this

select *, avg(ratings.rating) avgtag, count(distinct tag_id) distincttags videos, ratings  videos.id = ratings.video_id  , ratings.tag_id in (2, 12)  group video_id  having distincttags > 1 order avgtag desc 

the problem takes average of ratings if 3 users rated how funny video , 1 rated how interesting is, overall average skewed. want sort average rating of 1 tag plus average of tag divided 2.

---

here's sample data, requested.

tables:

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

example data 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 can see, average ratings of tag 2 85 , average ratings of tag 12 60. average of 2 72.5. don't want average of ratings because 75 , skewed towards 1 tag because there more of tag 2 of tag 12.

the first query posted orders average ratings of 1 tag. second query posted orders them ratings of both tags, isn't ideal because it's skewed.

i'm looking way order results (average ratings of tag 2 + average ratings of tag 12) / 2. hope makes sense!

select  video_id, avg(rating) rating    (         select  video_id, avg(rating) rating            ratings           tag_id in (2, 12)         group                 video_id, tag_id         ) q group         video_id order         rating desc