Sum of items in one table related to items in another table; 3rd table shows relationships

[rootl]

Greetings. I have 3 tables below. One shows 'posts' The second shows 'file_logs' It stores records of each time a file is downloaded. The third shows 'relationships' between posts and files. Usually two files are connected to 1 post.

I would like to show sums of 'file ids' grouped by their related post id.

For example, 2 files are connected to one post. I need the sum of how many times those 2 files are downloaded . What would be the best way to do this? Thank you.


1) Table "Posts":

p.id | p.title
1 |||| Title1
2 |||| Title2
3 |||| Title3



2) Table file_logs (records every time a file is downloaded):

f.id || f.file_id (id of which file is downloaded)
1 ||||||| 2
2 ||||||| 3
3 ||||||| 4
4 ||||||| 3
5 ||||||| 3
6 ||||||| 2
7 ||||||| 4

3) Table "relationships" ( stores relationship between posts and download files; often 2 files are connected to one post):

r.id (post p.id) | r.file_id (f.file_id)
1 ||||||||||||||||| 2
1 ||||||||||||||||| 3
2 ||||||||||||||||| 4
2 ||||||||||||||||| 5
3 ||||||||||||||||| 6
3 ||||||||||||||||| 7

 

[dhookom]

Please use the TGML Pre tag to display your data tables. This takes one click after selecting your table. Then always Preview prior to posting.

Duane
Hook'D on Access
MS Access MVP

 

[Olaf Doschke]

You only join the relationships and file_logs table to get the counts.

Select r.id as post_id, r.file_id, count(*) as "number of downloads"
from relationships r
inner join file_logs f on f.file_id = r.file_id

Not what you want?

YOu'll get two number of downloads for posts with two files. The lower one is the number of time both files were downloaded, of course not accouting for the user downloading. If you don't have user_id in your file_log you'll never know which users download both files.

Bye, Olaf.

 

[rootl]

Greetings,

My apologies for forgetting to use the pre tag. Olaf, thank you. I figured out what I need to do. I don't need user id in this case. Thanks very much. I will share in case others need to do something like this.

This counts totals of individual file downloads:

[pre]SELECT
p.id, p.post_title, count(d.download_id)
FROM
posts p
INNER JOIN file_log d
ON p.id = d.file_id
INNER JOIN relationships r
ON d.file_id = r.file_id
group by
r.rel_file_id[/pre]


This counts totals of files grouped by the post id they are associated with:

[pre]SELECT
p.id, p.post_title, count(d.download_id)
FROM
posts p
INNER JOIN file_log d
ON p.id = d.file_id
INNER JOIN relationships r
ON d.file_id = r.file_id
group by
r.rel_post_id[/pre]