Write a SELECT statement that counts and totals line items that have an ItemPric

Question

Write a SELECT statement that counts and totals line items that have an ItemPrice value that is greater than 400. This is actually a modification of the previous question.

4. Write a SELECT statement that returns one row for each customer that has orders with these columns:

The EmailAddress column 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 than 1 order

Here is my answer to the previous question. I have no idea if it is correct!

SELECT EmailAddress, COUNT (ord2.orderID) AS OrderCount,
   SUM((ItemPrice-DiscountAmount)*Quantity) As TotalAmount
FROM Customers AS cust JOIN Orders AS ord1
   ON cust.CustomerID = ord1.CustomerID
   JOIN OrderItems AS ord2
       ON ord2.orderID = ord1.orderID
GROUP BY EmailAddress
HAVING COUNT (ord1.OrderID) > 1
ORDER BY OrderCount DESC

The problem I am working on is this:

5. Modify the solution to exercise 4 so it only counts and totals line items that have an ItemPrice value that

Explanation / Answer

SELECT EmailAddress, COUNT (ord2.orderID) AS OrderCount
FROM Customers AS cust JOIN Orders AS ord1
   ON cust.CustomerID = ord1.CustomerID
   JOIN OrderItems AS ord2
       ON ord2.orderID = ord1.orderID

WHERE ord2.itemPrice > 400
GROUP BY EmailAddress
HAVING COUNT (ord1.OrderID) > 1
ORDER BY OrderCount DESC

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.