Any query about northwind?

[omegazafer]

Hi friends, I have a homework, i worked for hours 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 Good DAys Regards

[Paul Conrad]

Are you still having trouble?

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

[omegazafer]

Yes, I still can't find solution.

[Paul Conrad]

Don't you need a products.productname in the group by?

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

[omegazafer]

I need product name of max sold products in result table.