Jump to content

Count of records grouped by relationships in another table


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 post
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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...