Fetch Top 3 Sales People

This is one of those queries which when I first had to produce this for a technical test I thought would be easy enough till I attempted it!  Isn't it often the way; the stuff you think is going to be a walk in the park cos it sounds so simple usually turns out to be a real challenge. Anyhow if you learn how to use common table expressions it does make this kind of query a breeze. To illustrate how this works we're going to get the top 3  sales people from the AdventureWorks2012 database.

We'll tackle it in 3 stages:

• First get the total sales for each sales person
• Next wrap this query up in a common table expression
• Query your common table expression using a Top clause

Using a common table expression allows us to query the original query we wrote using order by to sort the totalled sales into decscending order by sales total and then then use a top clause to extract the top three.

-- First group the sales by sales person
 
select p.FirstName + ' ' + p.LastName as SalesPerson, sum(o.TotalDue)as TotalSales
from sales.SalesOrderHeader as o
join person.Person as p on p.BusinessEntityID = o.SalesPersonID
group by p.FirstName + ' ' + p.LastName

-- Next wrap the grouped sales into a common table expression which you can now query

with GroupedSales
as
(
select p.FirstName + ' ' + p.LastName as SalesPerson, sum(o.TotalDue)as TotalSales
from sales.SalesOrderHeader as o
join person.Person as p on p.BusinessEntityID = o.SalesPersonID
group by p.FirstName + ' ' + p.LastName
)
select SalesPerson, TotalSales
from groupedsales

-- Finally add a top statement

with GroupedSales
as
(
select p.FirstName + ' ' + p.LastName as SalesPerson, sum(o.TotalDue)as TotalSales
from sales.SalesOrderHeader as o
join person.Person as p on p.BusinessEntityID = o.SalesPersonID
group by p.FirstName + ' ' + p.LastName
)
select Top(3) SalesPerson, TotalSales
from GroupedSales
order by TotalSales desc