Any query about northwind? [modified]
-
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
-
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
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.
-
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.
I am sorry, but there are less people in sql form. Here, replies are faster and most of people in c# form know sql.
-
I am sorry, but there are less people in sql form. Here, replies are faster and most of people in c# form know sql.
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.