1

I'm trying to find the top 5 sales people based on their total sales. I want to show the rep's names, and their total sales, and I'm working in AdventureWorks.

The thing that keeps throwing me off is the GROUP BY statement. In the query below, I'm not sure why the GROUP BY isn't working. I'm sure there's a more efficient way to do this, but at this point I'm just trying to really learn joins and subqueries, and the way in which GROUP BY works seems to be something I'm not grasping.

SELECT SalesLT.Customer.SalesPerson, SUM(repjoin.TotalDue) OVER (PARTITION BY repjoin.salesperson)
FROM SalesLT.customer INNER JOIN
    (SELECT SalesLT.SalesOrderHeader.CustomerID, 
            SalesLT.SalesOrderHeader.TotalDue, 
            SalesLT.Customer.SalesPerson
    FROM SalesLT.Customer
        INNER JOIN SalesLT.SalesOrderHeader
        ON SalesLT.SalesOrderHeader.CustomerID = SalesLT.Customer.CustomerID
    ) repjoin
        ON SalesLT.Customer.CustomerID = repjoin.CustomerID
GROUP BY SalesLT.Customer.SalesPerson
Seth A Kelley
  • 33
  • 1
  • 4
  • what's the actual issue? Does this help? http://stackoverflow.com/questions/2404565/sql-server-difference-between-partition-by-and-group-by – RandomUs1r Mar 18 '13 at 22:28
  • Welcome to Stack Overflow! When posting questions, when possible, please remember to show your expected results and and the results you're getting. – Michael L. Mar 18 '13 at 23:55

1 Answers1

0

Why not simply 

SELECT TOP 5
  c.SalesPerson, 
  SUM(soh.TotalDue) TotalSales
FROM
  SalesLT.Customer c
  INNER JOIN SalesLT.SalesOrderHeader soh ON soh.CustomerID = c.CustomerID
GROUP BY 
  c.SalesPerson
ORDER BY
  SUM(soh.TotalDue) DESC

?

Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • This should be the answer you want. To explain, `GROUP BY` simply collapses the results based on the specified values. Grouping by `c.SalesPerson` collapses the results to one row per salesperson allowing you to use aggregate functions (such as `SUM()`, `AVG()`, `MIN()`, `MAX()`, etc) on the non-grouped columns in the results. Note, only the columns you grouped by can be be referred to without aggregate functions. – Michael L. Mar 18 '13 at 23:58