mysql - SQL - 基于平均值的 WHERE 子句？

Question

在我的 SQL 期末考试中，有一道题的意思是:

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.

我的回答:

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;

我尝试在 GROUP BY 上方 WHERE ave_shipping_amount > 5，但这没有用，因为 ave_shipping_amount 列不存在。
我还尝试了 WHERE ROUND(AVG(o.ship_amount), 2) > 5，但这是对组函数的错误使用。

这个问题怎么解决？这看起来很基本，但我正在疯狂地试图弄明白。

Answer 1

你需要使用 HAVING:

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

它就像一个 WHERE 子句，除了它是在聚合之后计算的