BYU-Studies
asked on
How do I count the top 10 most popular downloads in my downloads table?
Our website offers free articles, reviews, book chapters, and dissertations for download, and each time a customer downloads one, our code makes an entry in the download table that has the titleId foreign key (to identify the product) and a time stamp of when that product was downloaded. The Title table (that the titleId refers to) then has a TypeId that specifies what type of product was downloaded (article, review, etc.).
I need to write SQL that will get the top 10 articles downloaded, but I'm not sure how to do it. How do I know how many times each article appears in the download table, and then sort the articles by the number of times it appears so that I can do a "SELECT TOP 10" statement?
I need to write SQL that will get the top 10 articles downloaded, but I'm not sure how to do it. How do I know how many times each article appears in the download table, and then sort the articles by the number of times it appears so that I can do a "SELECT TOP 10" statement?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT TOP 10 titleid, COUNT(titleid) as Title FROM downloadtable GROUP BY titleid
Sorry, try the following..,
SELECT TOP 10 titleid, COUNT(titleid) as Title FROM downloadtable GROUP BY titleid ORDER BY DESC
SELECT TOP 10 titleid, COUNT(titleid) as Title FROM downloadtable GROUP BY titleid ORDER BY DESC
try this
SELECT TOP 10 A.*
FROM product A
(SELECT titleid, COUNT(titleid) numDownloads
FROM downloadtable
GROUP BY titleid ) B ON A.titleid = B.titleid
ORDER BY B.numDownloads DESC
ASKER
Perfect. Thanks! Here's the final statement I used - I added an ORDER BY clause at the end.
SELECT TOP (10) _Download.titleidfk, COUNT(_Download.titleidfk) AS Downloads
FROM _Download INNER JOIN
_Title ON _Download.titleidfk = _Title.titleId
WHERE (_Title.typeId = '2')
GROUP BY _Download.titleidfk
ORDER BY Downloads DESC
SELECT TOP (10) _Download.titleidfk, COUNT(_Download.titleidfk)
FROM _Download INNER JOIN
_Title ON _Download.titleidfk = _Title.titleId
WHERE (_Title.typeId = '2')
GROUP BY _Download.titleidfk
ORDER BY Downloads DESC
SELECT TOP 10 COUNT(titleid) as Title FROM downloadtable GROUP BY titleid