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.

I'm trying to put together a query to aggregate some information into a view, and am having trouble working out how to get that data I need from a couple of different tables. I'll start by describing the 3 tables I'm working with.

I have artists, which is as follows:

Ref | Artist
  1 | Metallica

I have tracks:

Filename | Title             | artist_ref
     001 | Sad But True      |       1
     002 | Through The Never |       1

And lastly I have popularity:

Filename | week | month | quarter
     001 |   23 |    42 |     138
     002 |    4 |    23 |      42

What I'm trying to do is get an aggregated popularity table formatted as so:

artist_ref | week | month | quarter
         1 |   27 |    65 |     180

I understand I'll have to use some kind of subquery and SUM, but my knowledge of this area is fairly limited. Any help is hugely appreciated as I'm finding this a bit of a brain buster!

share|improve this question
    
how artist record related with tracks and popularity? –  Praveen Prasannan Sep 16 '13 at 15:24
    
Oh sorry, I missed it off. I've edited the post to include the information. "artists" isn't directly associated with popularity, but it is with tracks, and so is popularity. –  n00b Sep 16 '13 at 15:27

1 Answer 1

up vote 1 down vote accepted
Select artist_ref, SUM(week) as week,
SUM(month) as month,SUM(quarter) as quarter
FROM tracks LEFT JOIN popularity
USING (Filename)
group by artist_ref;

SAMPLE FIDDLE

share|improve this answer
    
Excellent! With a bit of fiddling indexes that works an absolute charm. Thanks loads for your help. Here's my final query: Select artist_ref, IFNULL(SUM(week), 0) as week, IFNULL(SUM(month), 0) as month, IFNULL(SUM(quarter), 0) as quarter FROM tracks LEFT JOIN popularity USING (Filename) GROUP BY artist_ref –  n00b Sep 16 '13 at 15:58

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.