• Status: Solved
• Priority: Medium
• Security: Public
• Views: 431

T-sql

Hello:
Hello:
I really need help on solving the following problems.  I appreciate any input.
Please  refer to schema in the attached file.
Customer with the highest order amount on orders with 5 or more different items
see attached

Get Items with Qty greater than 10 per order.  Select OrderID, OrderDate, CategoryName, ItemName  and Item Qty.

Get-Customer.xls
0
gloriagalvez
• 2
1 Solution

Commented:
try this for the items with order qty greater than 10..

``````Select O.OrderID, O.OrderDate, C.CategoryName, I.ItemName, OD.Qty
from [Order] O inner join OrderDetail OD on O.OrderId=OD.OrderId
inner join Item I on OD.ItemID=I.ItemId
inner join Category C on I.CategoryID=C.CategoryID
inner join (SELECT OrderId, sum(OD.Qty) AS TQty
FROM   OrderDetail
GROUP BY OrderId) AS OD2 ON OD.OrderId = OD2.OrderId
where  OD2.TQty>10
``````
0

Commented:
this will be for the highest order amount ...

``````Select top 1 C.CustomerName, max(O.Amount) as MaxAmount
from [Order] O inner join OrderDetail OD on O.OrderId=OD.OrderId
inner join Customer C on O.CustomerID=C.CustomerID
inner join (SELECT OrderId, COUNT(DISTINCT ItemId) AS TItems
FROM   OrderDetail
GROUP BY OrderId) AS OD2 ON OD.OrderId = OD2.OrderId
where  OD2.TItems>5
group by C.CustomerName
order by max(O.Amount) desc
``````
0

Author Commented:
I test it and it worked like a charm thank you:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.