Using SQL Server, how can I find the topselling products by each salesperson in AdventureWorks2014?

Question

For a homework assignment, I am trying to find the topselling products by each salesperson in January 2012 using AdventureWorks2014.

Here is what I have so far:

SELECT      
    Person.Person.LastName, Person.Person.FirstName, 
    Person.Person.MiddleName, 
    Employee_1.JobTitle, 
    Sales.SalesPerson.SalesQuota, Sales.SalesOrderHeader.OrderDate, 
    Production.Product.Name, 
    SUM(distinct OrderQty) AS Expr2
FROM
    Sales.SalesOrderDetail 
INNER JOIN
    Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID 
INNER JOIN
    Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID 
                           AND Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID 
                           AND Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID 
INNER JOIN
    Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID 
                      AND Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID 
                      AND Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID 
                      AND Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID 
INNER JOIN
    HumanResources.Employee AS Employee_1 
INNER JOIN
    Person.Person ON Employee_1.BusinessEntityID = Person.Person.BusinessEntityID 
        ON Sales.SalesPerson.BusinessEntityID = Employee_1.BusinessEntityID 
        AND Sales.SalesPerson.BusinessEntityID = Employee_1.BusinessEntityID
WHERE       
    (Sales.SalesOrderHeader.OrderDate BETWEEN '2012-01-01' AND '2012-01-31')
GROUP BY 
    Person.Person.LastName, Person.Person.FirstName, Person.Person.MiddleName, 
    Employee_1.JobTitle, 
    Sales.SalesPerson.SalesQuota, Sales.SalesOrderHeader.OrderDate, 
    Production.Product.Name
ORDER BY  
    Person.Person.LastName, Production.Product.Name

I can not figure out how to add all of the orderqty for each individual product. In the top two rows of results I have the same product sold by the same person. I want to add those together and then find the top 5 products that each salesperson has?

Can anyone help?

Answer 1

My initial goal would be to reduce the aggregation dataset down to the fewest required number tables to perform the aggregation (SalesOrderHeader, SalesOrderDetail) and join that to the table with the additional information (e.g. Person, Employee).

I included the Product table in the aggregation subquery, but it could be done after the aggregation query and join to the ProductId (after adding it to the group by and select) instead.

---

There are many ways to do this, here are some:

cross apply version:

select
      p.LastName
    , p.FirstName
    , p.MiddleName
    , e.JobTitle
    , s.SalesQuota
    , s.OrderDate
    , s.ProductName
    , s.TotalQty 
  from Person.Person as p on 
    inner join HumanResources.Employee as e on e.BusinessEntityID = p.BusinessEntityID
    cross apply ( 
      select top 5 /* 5 rows */ 
        soh.SalesPersonID
      , p.ProductName
      , TotalQty = sum(OrderQty)
      from Sales.SalesOrderHeader as soh 
        inner join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
        inner join Production.Product as pr on sod.ProductID = pr.ProductID 
        where soh.OrderDate between '2012-01-01' and '2012-01-31'
          and s.SalesPersonID = p.BusinessEntityID /* per person */
        group by soh.SalesPersonID, p.ProductName
        order by sum(OrderQty) desc 
        /* ordered by sum(OrderQty) descending */
    ) s

top with ties version:

select top 5 with ties 
    p.LastName
  , p.FirstName
  , p.MiddleName
  , e.JobTitle
  , s.SalesQuota
  , s.OrderDate
  , s.ProductName
  , s.TotalQty 
    from Person.Person as p on 
      inner join HumanResources.Employee as e on e.BusinessEntityID = p.BusinessEntityID
      inner join ( 
        select 
          soh.SalesPersonID
        , p.ProductName
        , TotalQty = sum(OrderQty)
        from Sales.SalesOrderHeader as soh 
          inner join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
          inner join Production.Product as pr on sod.ProductID = pr.ProductID 
          where soh.OrderDate between '2012-01-01' and '2012-01-31'
          group by soh.SalesPersonID, p.ProductName
      ) on s.SalesPersonID = p.BusinessEntityID
order by row_number() over (partition by p.BusinessEntityID order by s.TotalQty desc)
/* returns all rows where row_number() over() evaluates to 1,2,3,4 ,or 5 */

common table expression with row_number() version:

with top5 as (
select
      p.LastName
    , p.FirstName
    , p.MiddleName
    , e.JobTitle
    , s.SalesQuota
    , s.OrderDate
    , s.ProductName
    , s.TotalQty 
    , rn=row_number() over (partition by p.BusinessEntityID order by s.TotalQty desc)
  from Person.Person as p on 
    inner join HumanResources.Employee as e on e.BusinessEntityID = p.BusinessEntityID
    inner join ( 
      select 
        soh.SalesPersonID
      , p.ProductName
      , TotalQty = sum(OrderQty)
      from Sales.SalesOrderHeader as soh 
        inner join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
        inner join Production.Product as pr on sod.ProductID = pr.ProductID 
        where soh.OrderDate between '2012-01-01' and '2012-01-31'
        group by soh.SalesPersonID, p.ProductName
    ) on s.SalesPersonID = p.BusinessEntityID
)

select
      LastName
    , FirstName
    , MiddleName
    , JobTitle
    , SalesQuota
    , OrderDate
    , ProductName
    , TotalQty 
  from top5
  where rn < 6