Link to home
Start Free TrialLog in
Avatar of BYU-Studies
BYU-StudiesFlag for United States of America

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?
Avatar of waltersnowslinarnold
waltersnowslinarnold
Flag of India image

Try the following..,

SELECT TOP 10 COUNT(titleid) as Title FROM downloadtable GROUP BY titleid
ASKER CERTIFIED SOLUTION
Avatar of ludofulop
ludofulop

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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

Open in new window

Avatar of BYU-Studies

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