You have a table orders with columns customer_id and status, where status can be NULL. You want to count orders by status, treating all NULL statuses as 'Pending'. Which query correctly achieves this?
hard📝 Application Q15 of 15
SQL - GROUP BY and HAVING
You have a table orders with columns customer_id and status, where status can be NULL. You want to count orders by status, treating all NULL statuses as 'Pending'. Which query correctly achieves this?
ASELECT status, COUNT(*) FROM orders GROUP BY status WHERE status IS NULL;
BSELECT status, COUNT(*) FROM orders GROUP BY status HAVING status IS NOT NULL;
CSELECT COALESCE(status, 'Pending') AS order_status, COUNT(*) FROM orders GROUP BY order_status;
DSELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY status;
Step-by-Step Solution
Solution:
Step 1: Replace NULL with 'Pending' using COALESCE
COALESCE(status, 'Pending') converts NULL statuses to 'Pending' for counting.
Step 2: Group by the alias used in SELECT
Grouping by order_status ensures all NULLs are counted under 'Pending'.
Final Answer:
SELECT COALESCE(status, 'Pending') AS order_status, COUNT(*) FROM orders GROUP BY order_status; -> Option C
Quick Check:
COALESCE + GROUP BY alias counts NULL as 'Pending' [OK]
Quick Trick:Use COALESCE and group by alias to count NULLs as desired [OK]
Common Mistakes:
MISTAKES
Filtering out NULLs instead of replacing them
Using WHERE after GROUP BY (syntax error)
Grouping by original column without COALESCE
Master "GROUP BY and HAVING" in SQL
9 interactive learning modes - each teaches the same concept differently
