CODEWITHSUNDEEP
mysql
PatTastic
PatTastic

Reputation: 114

SQL - WHERE clause based on an average?

In my SQL final exam, there was a question to the effect of:

Display the customer ID, the number of times the customer has placed an order, and the average shipping amount the customer has paid rounded to two decimal places. Only display results where the average shipping is greater than $5.

My answer:

SELECT c.customer_id, 
    COUNT(o.order_id) AS 'number_of_orders',
    ROUND(AVG(o.ship_amount), 2) AS 'ave_shipping_amount'
FROM customers c JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

I attempted WHERE ave_shipping_amount > 5 above the GROUP BY, but this didn't work as the ave_shipping_amount column doesn't exist.
I also tried WHERE ROUND(AVG(o.ship_amount), 2) > 5, but this was an incorrect use of a group function.

How would this question be solved? It seems basic enough, but I'm going insane trying to figure it out.

Upvotes: 1

Views: 722

Answers (1)

Paul Coldrey
Paul Coldrey

Reputation: 1439

You need to use HAVING:

HAVING ROUND(AVG(o.ship_amount), 2) > 5

It is like a WHERE clause except that it gets computed after aggregates

Upvotes: 2

Related Questions