dean2020 dean2020 - 11 days ago 6
SQL Question

SQL SELECT products WHERE 'average price per product' < value

I'd like to only return prod_id's that have an average price < 7. Looking at the sample below, the query should only return prod_id 1 (average price = 5)

+---------+-------+
| prod_id | price |
+---------+-------+
| 1 | 3 |
| 1 | 4 |
| 1 | 8 |
| 2 | 12 |
| 2 | 14 |
+---------+-------|


So the desired results should be:

+---------+-------+
| prod_id | avg_pr|
+---------+-------+
| 1 | 5 |
+---------+-------|


I tried this:

SELECT prod_id, AVG(price)
FROM products
WHERE AVG(price) < 7
GROUP BY prod_id


It's giving me this error: #1111 - Invalid use of group function

Answer

you can't use an aggregation function in the where clause, you need to use HAVING for that:

SELECT prod_id, AVG(price) 
  FROM products
  GROUP BY prod_id
  HAVING AVG(price) <7;
Comments