CODEWITHSUNDEEP
mysql
Psyche
Psyche

Reputation: 8783

Complex SQL query in MySQL

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.

Upvotes: 0

Views: 146

Answers (2)

Jay
Jay

Reputation: 27512

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.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146603

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

Upvotes: 3

Related Questions