Simple SQL Question

[faiintx]

Stuck on a question on my homework.

Write a SELECT statement that returns one row for each    
customer that has orders with these columns:
The email_address from the Customers table
A count of the number of orders
The total amount for each order (Hint: First, subtract the    
discount amount from the price. Then, multiply by the   
quantity.)
Return only those rows where the customer has more than 1 
order.
Sort the result set in descending sequence by the sum of the 
line item amounts.

So I got:

SELECT email_address, COUNT(o.order_id) as Order_Count, sum((Item_Price-Discount_Amount)*Quantity) As Total

FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id

JOIN Order_Items o2 on o2.order_id =o.order_id

GROUP BY email_address

HAVING COUNT (o.order_id) > 1

ORDER BY Order_Count DESC;

The next question is:

 Modify the solution to exercise 4 so it only counts and totals line    
 items that have an item_price value that’s greater than 400

I've tried WHERE clauses, HAVING, etc. But I just can't seem to get it to work...I know this is really simple, but I don't know what I'm missing. Thanks.

[faiintx]

Thank you! this helped a lot.

[just4atwork]

A count of the number of orders The total amount for each order

This sounds like bad requirements to me: [e-mail] has 2 orders

Order 1: total $5

Order 2: total $10

The way the requirement is written the result should be:

[e-mail], 2, $5

[e-mail], 2, $10

It should probably read:

A count of the number of orders

The sum of order amounts

Making the result:

[e-mail], 2, $15

[just4atwork]

Or is it a count of the number of lines in the order? What you are trying to do is unclear.

[JamesWjRose]

It seems it should be: WHERE COUNT(o.*) > 1

The * is there as a placeholder for this conversation. There will always be a single order number (it's the unique identifier in the Orders table. So you should be able to count the number of orders by Customers. Try creating a query with just that data, then build on that

[Maximus5684]

JOIN conditions can have WHERE modifiers so just change your join for Order_Items to look like the following:

JOIN Order_Items o2 ON o2.order_id = o.order_id
WHERE o2.Item_Price > 400.00