90

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?

Félix Adriyel Gagnon-Grenier
  • 8,422
  • 10
  • 52
  • 65
D-Lef
  • 1,219
  • 5
  • 14
  • 20

4 Answers4

156

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

Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
16

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
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
2

try this. WHERE (select (count(p.CAT) > 3) from TableName)

Mohamed Sabry
  • 21
  • 2
  • 1
    Awesome, this did work wonders for me. I was in a situation where I could only change what was inside an already existing WHERE (thus not able to use having). – Anders Hansson Jan 29 '23 at 07:18
0

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
user7872783
  • 1