[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I count the top 10 most popular downloads within the last month?

Posted on 2009-12-28
3
Medium Priority
?
291 Views
Last Modified: 2012-05-08
I have a download table that records every time an article is downloaded (_title.typeid=2 means the title is an article), and my SQL statement currently gets the top 10 most downloaded articles. My download table also has a column called dateTime to record exactly when each article is downloaded using a dateTime. Instead of getting the top 10 most downloaded articles of all time, I want to get the top 10 most downloaded articles within the last month, but I'm not sure how to do operations on the datetime.

How would I modify this SQL to only get the top 10 downloaded articles of the month?
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

Open in new window

0
Comment
Question by:BYU-Studies
3 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 1000 total points
ID: 26131566
here I assume you want the current month.. If you want a specific data then you should replace the GETDATE() with a variable
SELECT TOP (10) _Download.titleidfk, COUNT(_Download.titleidfk) AS Downloads
FROM _Download INNER JOIN
   _Title ON _Download.titleidfk = _Title.titleId
WHERE (_Title.typeId = '2')
  AND MONTH(dateTime) = MONTH(GETDATE())
GROUP BY _Download.titleidfk
ORDER BY Downloads DESC

Open in new window

0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 26131577
Try this..,

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
BETWEEN  DATEPART(mm,DateColumn) - 1 AND DATEPART(mm,DateColumn)
0
 

Author Closing Comment

by:BYU-Studies
ID: 31670407
Works great, thanks.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 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