Jump to content

Count of records grouped by relationships in another table


texteditor

Recommended Posts

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 by texteditor
Link to comment
Share on other sites

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...