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.

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.

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.