Data coalescence between two tables

[Geowil]

I have been attempting to crack this nut for several hours now but I cannot figure out what combination of query code is required to achieve the exact results I am looking for.

I have two tables:

Music and Playlists

The fields for Music:

ID (Primary), Track_Name, File_Path, Date_Added, Artist, Album

The fields for Playlists:

PID, ID (many-to-one relation with Music), Track_Name, File_Path, Artist, Album, Number_of_Plays

What I am trying to do is for each ID in Music combine all of the records in all Playlists and then sum the Number_of_Plays field

Here is as far as I have gotten:

Select Music.ID, Music.Track_Name, Playlists.PID From Music
Join Playlists On Music.ID = Playlists.ID
        Where Playlists.ID In (
              Select ID From Playlists             
              Group By ID              
              Having Count(Number_of_Plays) > 1)

The only thing this produces is a list ordered by ID of records that have more than one number of plays. I have not figure out how to only show each song record once and then sum the number of plays for that song across all playlists.

[Geowil]

I managed to figure it out:

Select Music.ID, Music.track_name, Playlists.PI,     sum(Playlists.Number_of_Plays) As Total_Plays From Music
Join Playlists On Music.ID = Playlists.ID
Group By Playlist.ID, LEFT('ID', 11) Having Count(Playlist.ID) > 1

This produced a resultset that had the records ordered by ID and had the number of plays summed.

[Alberto Bucur]

Try this first.
The inner select sum the number of plays from Playlist, this is joined with Music table trough WHERE clause.

select m.ID, m.track_name s.Total_Plays
from Music m,
(select ID, sum(Number_of_Plays) as Total_Plays 
    from Playlists
    group by ID) s
 where m.ID = s.ID