0

I have the following tables in the database:

bands
- band_id
- band_name

albums  
- album_id 
- album_name  
- album_owner  

songs
- song_id
- song_name
- song_owner

ratings
- rating_id
- rating_value
- rating_song

Tables albums and songs relate to band_id from table bands by album_owner and song_owner.

Table ratings relates to song_id from table songs by rating_song.

Given the tables above how can I get all the bands and the count of albums, song and rating for each one? The ratings must be returned as the sum of rating_value divided by the number of rates.

Improve this question
3
  • Can you give an example of what you would like the output to look like? Commented Aug 28, 2009 at 16:30
  • 2
    Firstly, I suggest better naming conventions for your tables. Instead of album_owner/song_owner, call it band_id. Instead of rating_song, call it song_id. This is so that a person can instantly know the relationship between the tables. Commented Aug 28, 2009 at 16:30
  • waqasahmed makes a good point, his scheme is also more compatible with a lot of ORMs (RoR and Cake come to mind). Commented Aug 28, 2009 at 16:43

2 Answers 2

Reset to default
3

try this

Select Band_Id, Band_Name,
  (Select Count(*) From Albums 
   Where Album_Owner = B.Band_Id) AlbumCount,
  S.Song_Id, S.Song_Name,
  Avg(rating_value) Rating
From Bands B 
  Left Join Songs S 
      On S.Song_Owner = B.Band_Id
  Left Join Ratings R 
      On R.rating_song = S.Song_Id
Group By Band_Id, Band_Name, S.Song_Id, S.Song_Name
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I get no result and it actually crashes the server even with a limit of 0,10.
0

To do it all in one query, you need to use subqueries.

select band_name,
(select count(*)
from album
where album_owner=band_id) as album_count,
(select count(*)
from song
where song_owner=band_id) as song_count,
(select avg(rating_value)
from song
join rating on rating_song=song_id
where song_owner=band_id) as average_rating
from band

I'm assuming here that you mean you want the average rating for all songs for each band. If you meant you want the average rating for each song, then it doesn't really make sense to do it as a single query, because the count of number of albums applies to a band, not to a song, though I suppose you could return one row per song with the number of albums for the band repeated on each song.

Improve this answer

2 Comments

Yes, average rating for all songs.
It crashes the server even with a limit of 0,10.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.