select group by count

Question

i have two tables with the following schema

files
------
id
name

the second table

permission
-----------
id
file_id
downloads
code

now i need to select the top five downloaded files from the downloads table, noting that a file_id can appear many times because of the download code code

this is what i have

Select
  files.name,
  permission.downloads
From
  files Inner Join
  permission On permission.file_id = files.id
Order By
  permission.downloads Desc
Limit 5

thi select the top five as i needed, but repeats the file names, is there anyway i can sum the download count for each file so no filename is repeated

Answer 1

you have to use group by

select
  f.name,
  sum(p.downloads) as downloads
from files as f
  inner join permission as p on p.file_id = f.id
group by f.name
order By sum(p.downloads) desc
limit 5