I am trying to modify the following query so the solution only counts and totals

Question

I am trying to modify the following query so the solution only counts and totals line items that have an item_price value that’s greater than 400. But not sure if I should be using HAVING or WHERE clause.

SELECT email_address, COUNT(DISTINCT oi.order_id) AS num_orders, SUM((item_price - discount_amount) * quantity) AS total_amount

FROM customers c JOIN orders o

ON c.customer_id = o.customer_id

JOIN order_items oi

ON oi.order_id = o.order_id

GROUP BY email_address

HAVING COUNT(oi.order_id) > 1

ORDER BY SUM(item_price) DESC;

Explanation / Answer

where clause will be used

and there will be some changes to query as aggregate function works on one column at a time

SELECT email_address, count(oi.order_id) AS num_orders, (SUM(item_price) - sum(discount_amount) ) * sum(quantity) AS total_amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items oi ON oi.order_id = o.order_id where item_price > 400 GROUP BY email_address HAVING count(oi.order_id) > 1 order by sum(item_price) desc

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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