How to avoid error "aggregate functions are not allowed in WHERE"
MysqlSqlAggregate FunctionsMysql Problem Overview
This sql code throws an
> aggregate functions are not allowed in 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;
How can I avoid this error?
Mysql Solutions
Solution 1 - Mysql
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).
Solution 2 - Mysql
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
Solution 3 - Mysql
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