Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Don't be afraid to say "I need help"
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

11
June

How Do I Calculate the Top N Sales by Group using SQL?

During a recent SQL Server Reporting Services 2008 R2 consultancy the client wanted to know how to return the Top N Sales by Group, using SQL.


Solution:

To achieve this one has to create a self-join, in this case using the Orders table, and a ranking.

To give some flexibility, instead of hard coding the Top N value, a parameter has been added:

Declare @TopN as Int
Set @TopN =5   
                           
SELECT     Employee.[First Name] + ' ' + Employee.[Last Name] AS [Employee Name], a.[Order Amount], COUNT(*) AS Rank_No
FROM         Orders AS a INNER JOIN
                      Orders AS b ON a.[Employee ID] = b.[Employee ID] AND a.[Order Amount] <= b.[Order Amount] INNER JOIN
                      Employee ON b.[Employee ID] = Employee.[Employee ID]
GROUP BY a.[Employee ID], a.[Order Amount],
Employee.[First Name] + ' ' + Employee.[Last Name]
HAVING      (COUNT(*) <= @TopN)
ORDER BY a.[Employee ID], Rank_No



If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us

MAKE A COMMENT

Name *

Email Address *

Comment *