Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    2

    Unanswered: Display Top 5 Salesman

    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.

    Thanx

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    look in BOL for details on aggregate functions. pay close attention to SUM().


  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Code:
    SELECT TOP 5 
    	dbo.Employees.Country, 
    	dbo.Employees.LastName,
    	dbo.Employees.FirstName, 
    	SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) AS Price
    FROM 
    	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
    WHERE 
    	(dbo.Orders.ShippedDate BETWEEN 
    	CONVERT(DATETIME, '1997-01-01 00:00:00', 102) 
    	AND CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
    GROUP BY 
    	dbo.Employees.Country, 
    	dbo.Employees.LastName, 
    	dbo.Employees.FirstName
    ORDER BY 
    	SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) DESC
    Last edited by vich; 04-01-08 at 04:02. Reason: formatting

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    aw shucks. that's one less homework problem OP will have to solve...

  5. #5
    Join Date
    Mar 2008
    Posts
    2

    Thanx

    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.

    Thanx again - Kudos

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •