WITH playlist_info AS ( SELECT pt.playlist_id, p.name AS playlist_name, t.name AS track_name, (t.milliseconds / 1000) AS length_seconds FROM track AS t LEFT JOIN playlist_track AS pt ON t.track_id = pt.track_id LEFT JOIN playlist AS p ON pt.playlist_id = p.playlist_id ) SELECT playlist_id, playlist_name, COUNT(track_name) AS number_of_tracks, SUM(length_seconds) AS length_seconds FROM playlist_info GROUP BY 1 ORDER BY 1
What I expected to happen:
Correct Output Table
What actually happened:
No error, just incorrect data.
Curious why my code does not output the correct data. Other problems don’t seem to have the same issue. Maybe it has to do with using the LEFT JOIN which requires a certain order? The only difference between my answer and the solution is the order of the JOINs. Do I have to switch them around, or is there a different way to fix this by keeping them in the same JOIN order? Any help would be great!