Query Total Amounts for Orders where the amount of each item was greater than >100

Question

Using the following two tables on SQL Server 2005, how would I write a query to return the First_Name, Last_Name, Order_Date and total amount of an order where any order line item amounts for any order (OD_Amount) are greater than 100.

Orders

Order_ID
First_Name
Last_Name
Order_Date

Order_Details

Order_Detail_ID
Order_ID
OD_Item_No
OD_Amount

Answer 1

This is when you want each individual detail order to be greater than 100

SELECT t1.First_Name, t1.Last_Name, t1.Order_Date, SUM(t2.OD_Amount) AS 'totalAmount'
FROM Orders AS t1
JOIN Order_Details AS t2 ON t1.Order_ID = t2.Order_ID
WHERE t2.OD_Amount > 100
GROUP BY t1.First_Name, t1.Last_Name, t1.Order_Date

If you wanted each order itself to be over 100 then:

SELECT t1.First_Name, t1.Last_Name, t1.Order_Date, SUM(t2.OD_Amount) AS 'totalAmount'
FROM Orders AS t1
JOIN Order_Details AS t2 ON t1.Order_ID = t2.Order_ID    
GROUP BY t1.First_Name, t1.Last_Name, t1.Order_Date
HAVING SUM(t2.OD_Amount) > 100

Answer 2

SELECT
    O.first_name,
    O.last_name,
    O.order_date,
    SUM(OD.amount)
FROM
    Orders O
INNER JOIN Order_Details OD ON OD.order_id = O.order_id
WHERE EXISTS
(
    SELECT
    FROM
        Order_Details OD2
    WHERE
        OD2.order_id = O.order_id AND
        OD2.amount > 100
)
GROUP BY
    O.first_name,
    O.last_name,
    O.order_date

Answer 3

Something like this maybe?

Select 
  t1.Order_ID, First_Name, Last_Name, Order_Date, Sum(OD_Amount) as Order_Total
From
  Orders t1
Inner Join
  Order_Details t2
    on t1.Order_ID = t2.Order_ID
Where 
  t1.Order_Id in (Select Distinct Order_Id from Order_Details where OD_Amount > 100)
Group By
  t1.Order_ID, First_Name, Last_Name, Order_Date

Answer 4

I believe this is what you're looking for:

SELECT o.First_Name, o.Last_Name, sum(od.OD_Amount) As Order_Total_Amount FROM Order_Details AS od, Orders AS o WHERE o.Order_ID = od.Order_ID AND o.OrderID IN (SELECT Order_ID FROM Order_Details WHERE OD_Amount > 100) GROUP BY o.First_Name, o.Last_Name

Answer 5

I believe this is what you are looking for. The EXISTS clause limits the results to only orders that have a single line item > 100. The GROUP BY accounts for multiple orders placed on the same day by including the Order_ID column.

SELECT
  o.First_Name
 ,o.Last_Name
 ,o.Order_Date
 ,SUM(od.OD_Amount) AS Total_Amount
FROM Orders o
  INNER JOIN Order_Details od ON od.Order_ID = o.Order_ID
WHERE EXISTS(SELECT *
             FROM Order_Details od2
             WHERE od2.OD_Amount > 100
               AND od2.Order_ID = o.Order_ID)
GROUP BY
  o.Order_ID
 ,o.First_Name
 ,o.Last_Name
 ,o.Order_Date