I have a mySQL posts table, a files table, and a relationships table.
I need to sum file download counts grouped by the post the files are associated with.
SELECTp.id, p.post_title, count(d.download_id) FROM posts p INNER JOIN file_log d ON p.id = d.file_idgroup by p.post_title
The above query works as long as the titles of the files are the same, which is not always the case. I really need to group them by their relationship to the post they are associated with.
The relationships are stored in relationships r, which shows r.post_id (which is p.id from posts), and r.file_id (which is d.file_id from file_log)
I have tried a few subqueries, but I am not sure how to join the relationships table to my query and group counts by r.post_id
Thanks for any help.