How do I count the top 10 most popular downloads in my downloads table?

Posted on 2009-12-28
Last Modified: 2012-05-08
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?
Question by:BYU-Studies
    LVL 7

    Expert Comment

    Try the following..,

    SELECT TOP 10 COUNT(titleid) as Title FROM downloadtable GROUP BY titleid
    LVL 15

    Accepted Solution

    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
    LVL 7

    Expert Comment

    SELECT TOP 10 titleid, COUNT(titleid) as Title FROM downloadtable GROUP BY titleid
    LVL 7

    Expert Comment

    Sorry, try the following..,

    SELECT TOP 10 titleid, COUNT(titleid) as Title FROM downloadtable GROUP BY titleid ORDER BY DESC
    LVL 26

    Expert Comment

    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


    Author Closing Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article describes some very basic things about SQL Server filegroups.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now