[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

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?
0
BYU-Studies
Asked:
BYU-Studies
1 Solution
 
waltersnowslinarnoldCommented:
Try the following..,

SELECT TOP 10 COUNT(titleid) as Title FROM downloadtable GROUP BY titleid
0
 
ludofulopCommented:
try this:
select TOP 10 download.titleID, count(download.titleID) FROM download, Title WHERE download.titleId=Title.titleId AND Title.titleId.TypeId='article' GROUP BY download.titleID
0
 
waltersnowslinarnoldCommented:
SELECT TOP 10 titleid, COUNT(titleid) as Title FROM downloadtable GROUP BY titleid
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
waltersnowslinarnoldCommented:
Sorry, try the following..,


SELECT TOP 10 titleid, COUNT(titleid) as Title FROM downloadtable GROUP BY titleid ORDER BY DESC
0
 
tigin44Commented:
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

0
 
BYU-StudiesAuthor Commented:
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
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now