Just a straight forward question: In the SQL SVR 2000 Northwind database, how do I display the top 5 salesman? I would like to display the salesmen and the total amount of their sales to date. I have this as a starting point:
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between '19970101' And '20051231'
It displays all the sales between the dates, I just need to work something that will total up the sales for each employee then list the top 5.
SELECT TOP 5
SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) AS Price
dbo.Employees INNER JOIN
dbo.Orders ON dbo.Employees.EmployeeID = dbo.Orders.EmployeeID INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID
CONVERT(DATETIME, '1997-01-01 00:00:00', 102)
AND CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) DESC
Last edited by vich; 04-01-08 at 03:02.
I wish this was a homework assignment :-). It has been sometime since I had worked with SQL (5 + yrs) like anything, when you get asked a question, your initial response is: "No problem, I can do that." I am working on an older SQL application for a client and I just could not hammer out a top 5 sales query for their sales people. I had figured it out via an Access front-end and was this morning converting it to SQL when I saw this.