Take the 2-minute tour ×
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

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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

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
add comment

Your Answer

 
discard

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.