How to avoid error “aggregate functions are not allowed in WHERE”

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?

Enquirer: D-Lef

||

Solution #1:

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).

Respondent: Aziz Shaikh

Solution #2:

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
Respondent: Saharsh Shah

Solution #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
Respondent: user7872783

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.