|
Execute the following
SQL Servr T-SQL script in Query Editor to find the top 10 salesperson for each product(group) sold by AdventureWorks Cycles.
The ROW_NUMBER function creates a sequence number in descending sales order for each product in the CTE. The main SELECT query filters the first 10 in each subset for the final report:
USE AdventureWorks;
-- SQL group by top n - SQL row_number - SQL over partition by - SQL cte
WITH cteTop10Sales
AS (SELECT ROW_NUMBER()
OVER(PARTITION BY sod.ProductID
ORDER BY SUM(sod.LineTotal) DESC) AS SeqNo,
FirstName + ' ' + LastName AS [Name],
ProductName = p.Name,
'$' + convert(varchar,convert(money,SUM(sod.LineTotal)),
1) AS TotalBySalesPerson,
p.ProductNumber,
sod.ProductID
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Person.Contact c
ON soh.SalesPersonID = c.ContactID
WHERE soh.SalesPersonID IS NOT NULL
GROUP BY FirstName + ' ' + LastName,
sod.ProductID,
p.ProductNumber,
p.Name)
SELECT *
FROM cteTop10Sales cte
-- Display top 10 for each group in sql server
WHERE SeqNo <= 10
ORDER BY ProductID,
SeqNo
GO
/* Partial results
SeqNo Name ProductName TotalBySalesPerson
1 Linda Ecoffey ML Road Frame-W - Yellow, 38 $53,080.46
2 Gail Erickson ML Road Frame-W - Yellow, 38 $49,219.48
3 Maciej Dusza ML Road Frame-W - Yellow, 38 $31,212.35
4 Jauna Elson ML Road Frame-W - Yellow, 38 $30,887.90
5 Shelley Dyck ML Road Frame-W - Yellow, 38 $29,427.86
6 Carol Elliott ML Road Frame-W - Yellow, 38 $25,437.09
7 Carla Eldridge ML Road Frame-W - Yellow, 38 $23,814.83
8 Mark Erickson ML Road Frame-W - Yellow, 38 $22,062.78
9 Michael Emanuel ML Road Frame-W - Yellow, 38 $11,096.28
10 Martha Espinoza ML Road Frame-W - Yellow, 38 $9,052.23
*/
Related articles:
More with SQL Server 2005 : Top n Per Group, Paging, and Common Table Expressions
Who's On First? Solving the Top per Group Problem (Part 1: Technique)
|
