How do you filter groups to those with more than 5 orders?

How do you filter groups to those with more than 5 orders?

• A. SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 5;
• B. SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id WHERE COUNT(*) > 5;
• C. SELECT customer_id, SUM(*) FROM orders GROUP BY customer_id HAVING SUM(*) > 5;
• D. SELECT customer_id, COUNT(*) FROM orders;

Answer: (B)

Grouping data and filtering by aggregates means you first aggregate rows into groups, then apply a condition to those groups. To keep only customers with more than five orders, count how many orders belong to each customer and then restrict the results to groups where that count exceeds five. You group by customer_id, compute the count of orders for each group, and use a HAVING clause to require COUNT(*) > 5. HAVING runs after the grouping and filtering on aggregates, while WHERE runs before grouping and can’t reference aggregate results. So the correct pattern is: select customer_id, COUNT(*) as order_count from orders group by customer_id having COUNT(*) > 5. The other options fail because they either try to filter with WHERE on an aggregate, misuse SUM(*), or omit proper grouping and filtering altogether.

Grouping data and filtering by aggregates means you first aggregate rows into groups, then apply a condition to those groups. To keep only customers with more than five orders, count how many orders belong to each customer and then restrict the results to groups where that count exceeds five. You group by customer_id, compute the count of orders for each group, and use a HAVING clause to require COUNT() > 5. HAVING runs after the grouping and filtering on aggregates, while WHERE runs before grouping and can’t reference aggregate results. So the correct pattern is: select customer_id, COUNT() as order_count from orders group by customer_id having COUNT() > 5. The other options fail because they either try to filter with WHERE on an aggregate, misuse SUM(), or omit proper grouping and filtering altogether.