Getting Averages of Averages

[Petsmacker]

I run a site where people can rate articles written by members (user_id). These articles can then be rated by others on a special scale.

 

TABLE: articles -

id | user_id | title

1  |    3      | Transformers

2  |    5      | Shrek

3  |    2      | Shrek

 

TABLE: article_ratings -

article_id | rating_x | rating_y

1              |    4      |    5

2              |    3      |    2

2              |    4      |    4

3              |    2      |    5

2              |    5      |    3

3              |    4      |    4

 

Basically what I'm trying to do is get the average of the averages for a specific article. Example: I want to know the average of the average ratings for all the articles on 'Shrek'

 

The Shrek article ID's are 2 and 3

 

The average rating for Shrek with ID:2 are:

rating_x = 4

rating_y = 3

 

The average rating for Shrek with ID:3 are:

rating_x = 2

rating_y = 3

 

Then average those both together to bring a result of:

rating_x = 3

rating_y = 3

^^^^^^^^^^ And this result just here is the kind of output I want my database to provide.

 

I'm able to get the averages alone but am not able to get the average of the averages. I'm using PHP and SQL if that helps.

 

I'm thinking something along the lines of:

SELECT AVG(AVG(article_ratings.rating_x)), AVG(AVG(article_ratings.rating_y )) FROM article_ratings, articles WHERE articles.title='Shrek' AND articles_rating.article_id=articles.id

Obviously, two AVG()'s overlapping aren't allowed and my SQL isn't great as you can see so I hope someone can help.

[fenway]

You need to get the first round of averages, place those in a derived table, then average again.