Group by and average from related tables Ok so this is kinda weird I'm not sure how to exactly tackle it without it being a massive query, that might be slow so this is the tables as it is

Concerts:
name | country_id | ticket_price
foo    2            50
bar    1            40

Country:
id | name
1    UK
2    US

Region:
id | name
1    EU
2    America

Country_Region:
id | country_id | region_id
1    1             1
2    2             2


of course this is fictitious data/ schema but it does exactly what the real one does, I need to keep the country/region as a many to many so now what I need to do is get an avarage of say the ticket_price per month per region.

I'm really not sure how to get that query.

Thanks

Edit: I should also point out that there aren't just 2 regions and more can be added the whole time and country within them changed.
Are you looking for something along the lines of:

SELECT r.name as Region,
    avg(conc.ticket_price) as AverageCost
FROM Region r
INNER JOIN Country_Region cr
    ON cr.region_id = r.id
INNER JOIN Concerts c
    ON c.country_id = cr.country_id
GROUP BY r.name


This should not be a slow query at all - especially if the id fields are all indexed.

I recommend reading up on MySQL Aggregate Functions which use Group By from here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html