Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

name | country_id | ticket_price
foo    2            50
bar    1            40

id | name
1    UK
2    US

id | name
1    EU
2    America

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.


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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Are you looking for something along the lines of:

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

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:

share|improve this answer
Ya this is what I ended up doing in the end, and all the id fields are indexed so that's no problem, thanks man :) – user1713940 Dec 12 '13 at 16:10
Glad to hear that, no problem! – Anthony Clark Dec 12 '13 at 16:29

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.