Find top sales people

Question

I want to find Top and botton 10% sales people.How can I do this using SQL 2005 or 2008?

DECLARE @Sales TABLE 
(
SalesPersonID varchar(10), TotalSales int
)


INSERT @Sales
SELECT 1, 200 UNION ALL
SELECT 2, 300 UNION ALL
SELECT 7, 300 UNION ALL
SELECT 4, 100 UNION ALL
SELECT 5, 600 UNION ALL
SELECT 5, 600 UNION ALL
SELECT 2, 200 UNION ALL
SELECT 5, 620 UNION ALL
SELECT 4, 611 UNION ALL
SELECT 3, 650 UNION ALL
SELECT 7, 611 UNION ALL
SELECT 9, 650 UNION ALL
SELECT 3, 555 UNION ALL
SELECT 9, 755 UNION ALL
SELECT 8, 650 UNION ALL
SELECT 3, 620 UNION ALL
SELECT 5, 633 UNION ALL
SELECT 6, 720 
GO

Also If i add department, then how can i write same query to find top 10% and bottom 10% in each department? I please want both queries.

Answer 1

--Top 10%
SELECT TOP 10 PERCENT SalesPersonID, SUM(TotalSales) FROM @Sales
GROUP BY SalesPersonID
ORDER BY SUM(TotalSales) ASC

--Bottom 10%
SELECT TOP 10 PERCENT SalesPersonID, SUM(TotalSales) FROM @Sales
GROUP BY SalesPersonID
ORDER BY SUM(TotalSales) DESC

If you added a column Department varchar(20) for example:

--By Dept
SELECT TOP 10 PERCENT Department, SUM(TotalSales) FROM @Sales
GROUP BY Department
ORDER BY SUM(TotalSales) ASC/DESC //(Whichever one you want)

Answer 2

TOP 10 %

select top 10 percent SalesPersonID, sum(TotalSales)
  from Sales
 order by sum(TotalSales)
 group by SalesPersonID

BOTTOM 10 %

select top 10 percent SalesPersonID, sum(TotalSales)
  from Sales
 order by sum(TotalSales) desc
 group by SalesPersonID

Answer 3

cte version:

DECLARE @Sales TABLE (SalesPersonID varchar(10), TotalSales int)INSERT @Sales
SELECT 1, 200 UNION ALL
SELECT 2, 300 UNION ALL
SELECT 7, 300 UNION ALL
SELECT 4, 100 UNION ALL
SELECT 5, 600 UNION ALL
SELECT 5, 600 UNION ALL
SELECT 2, 200 UNION ALL
SELECT 5, 620 UNION ALL
SELECT 4, 611 UNION ALL
SELECT 3, 650 UNION ALL
SELECT 7, 611 UNION ALL
SELECT 9, 650 UNION ALL
SELECT 3, 555 UNION ALL
SELECT 9, 755 UNION ALL
SELECT 8, 650 UNION ALL
SELECT 3, 620 UNION ALL
SELECT 5, 633 UNION ALL
SELECT 6, 720 


;with a as
(
    select SalesPersonID, sum(TotalSales) as Total
    from @Sales
    group by SalesPersonID
)
select coalesce(a.SalesPersonID, b.SalesPersonID) as SalesPersonID, coalesce(a.Total,b.Total) as Total
from a a
    full outer join a b
     on a.SalesPersonID=b.SalesPersonID
where a.SalesPersonID in (select top 10 percent SalesPersonID from a order by Total desc)
    or b.SalesPersonID in (select top 10 percent SalesPersonID from a order by Total)
order by a.Total desc