Problem
Task
Return product categories whose average price is at least 50.
Schema
Table Schema
products(id, title, category, price)Input
Sample Data
| id | title | category | price |
|---|---|---|---|
| 1 | Keyboard | Accessories | 40 |
| 2 | Mouse | Accessories | 20 |
| 3 | Monitor | Displays | 220 |
| 4 | Desk Lamp | Home | 55 |
Output
Expected Output
| category | avg_price |
|---|---|
| Displays | 220 |
| Home | 55 |
Answer
Check Your Solution
Show Answer and Explanation
Correct Answer
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) >= 50
ORDER BY category;Explanation
AVG(price) is calculated after products are grouped by category. Because the condition depends on that aggregate result, it belongs in HAVING. ORDER BY makes the displayed category order stable.
Common Mistakes
- Putting AVG(price) >= 50 in WHERE.
- Selecting title even though the result is grouped by category.
- Filtering price >= 50 before grouping, which changes the average.
Concepts
Related Concepts
GROUP BY
HAVING
AVG
Next practice