texteditor Posted April 3, 2015 Share Posted April 3, 2015 (edited) 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. Edited April 3, 2015 by texteditor Link to comment Share on other sites More sharing options...
justsomeguy Posted April 6, 2015 Share Posted April 6, 2015 p.id = d.file_idThat means that post.id is the same as file_log.file_id, is that correct? Link to comment Share on other sites More sharing options...
texteditor Posted April 6, 2015 Author Share Posted April 6, 2015 I figured it out! Thanks @justsomeguy. SELECT p.id, p.post_title, count(d.download_id) FROM posts p INNER JOIN file_log d ON p.id = d.file_idINNER JOIN relationships r ON d.file_id = r.file_idgroup by r.rel_file_id The above query counts individual file download totals SELECT p.id, p.post_title, count(d.download_id) FROM posts p INNER JOIN file_log d ON p.id = d.file_idINNER JOIN relationships r ON d.file_id = r.file_idgroup by r.rel_post_id The above query counts total combined downloads. Files are grouped by the post id they are associated with. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now