如何避免错误“ WHERE 中不允许聚合函数”

此 sql 代码引发一个

在 WHERE 中不允许使用聚合函数

SELECT o.ID ,  count(p.CAT)
FROM Orders o
INNER JOIN Products p ON o.P_ID = p.P_ID
WHERE count(p.CAT) > 3
GROUP BY o.ID;

我如何避免这个错误？

128469 次浏览

小开

最佳答案

Replace WHERE clause with HAVING, like this:

SELECT o.ID ,  count(p.CAT)
FROM Orders o
INNER JOIN Products p ON o.P_ID = p.P_ID
GROUP BY o.ID
HAVING count(p.CAT) > 3;

HAVING is similar to WHERE, that is both are used to filter the resulting records but HAVING is used to filter on aggregated data (when GROUP BY is used).

小开

Use HAVING clause instead of WHERE

Try this:

SELECT o.ID, COUNT(p.CAT) cnt
FROM Orders o
INNER JOIN Products p ON o.P_ID = p.P_ID
GROUP BY o.ID HAVING cnt > 3

小开

Will self join will go for a toss with join where we have a condition to list prices that are greater than the median of the prices listed in the order table?

Eg. order_item, Order_Price

Since aggregate functions cannot be used in a WHERE clause >

select a.order_item_product_price, count(distinct
a.order_item_product_price) from
default.order_items a , default.order_items b
where a.order_item_product_price = b.order_item_product_price
group by a.order_item_product_price
having a.order_item_product_price > appx_median(b.order_item_product_price)
order by a.order_item_product_price limit 10