Any query about northwind? [modified]

[omegazafer]

Hi friends, I have a homework, i worked for days but I got stuck in a point. I can't go out of this point. I am very glad if you can help me. I make effort to get a query from from northwind database sample. I want max sales of employees according to product. There are 9 employees(sales persons). There are 77 products type. Salespersons sell this products. I want ,which product was sold maximumly according to sales turnover. The result must be like below salesperson product sales amount salesperson1 product a x salesperson2 product b y . . . . . . salesperson9 product h w. I tryed below script, I can find max sales of each salespersons but I can't add productname column to this query. select t.firstname,t.lastname,max(t.sales) as sales from (select employees.Firstname,employees.lastname,products.productname, sum("order details".unitprice*quantity*(1-discount)) as Sales from ((Employees inner join orders on employees.employeeID=orders.employeeID) inner join "order details" on orders.orderID="order details".orderID) inner join products on products.productID="order details".productID group by employees.lastname,employees.firstname,products.productname ) as t group by t.firstname,t.lastname order by t.firstname Note: I got answer from Jacob Sebastian like below, bu i didn't understand exactly that response. It works but I didn't understand, especially top1 clause. The result consists of 9 row but top1 gives one row according to I know and link between a and b is very complex. SELECT FirstName, LastName, (SELECT TOP 1 ProductName FROM (SELECT e.FirstName, e.LastName, p.ProductName, SUM(od.UnitPrice * od.Quantity*(1-Discount)) AS Sales FROM Orders oh INNER JOIN [Order Details] od ON oh.OrderID = od.OrderID INNER JOIN Products p ON p.ProductID = od.ProductID INNER JOIN Employees e ON e.EmployeeID = oh.EmployeeID GROUP BY e.FirstName, e.LastName, p.ProductName ) b WHERE a.FirstName = b.FirstName AND a.LastName = b.LastName ORDER BY Sales DESC ) AS ProductName, MAX(Sales) FROM ( SELECT e.FirstName, e.LastName, p.ProductName, SUM(od.UnitPrice * od.Quantity*(1-Discount)) AS Sales FROM Orders oh INNER JO

[Pete OHanlon]

Please post this on the SQL forum, especially as you have had this answered before. It's only polite to continue this on the original thread.

Deja View - the feeling that you've seen this post before.

My blog | My articles

[omegazafer]

I am sorry, but there are less people in sql form. Here, replies are faster and most of people in c# form know sql.

[Pete OHanlon]

omegazafer wrote:

I am sorry, but there are less people in sql form. Here, replies are faster and most of people in c# form know sql.

That's an abuse of the forum system. How do I find an answer to a problem in future if it's not in a logical area? Why not ask a C# question in the C++ forum because the syntax is a little bit similar? There's a reason why these forums are organised the way they are, and it's not to provide a shortcut for somebody who's too lazy or incompetent to use the appropriate forum.

Deja View - the feeling that you've seen this post before.

My blog | My articles