You have two tables: Orders2022: order_id, customer_id, total_amount 1, 101, 250 2, 102, 300 Orders2023: order_id, customer_id, total_amount 2, 102, 350 3, 103, 400 Write a query to list all unique customer_ids from both years along with the sum of their total_amounts.
ASELECT customer_id, SUM(total_amount) FROM Orders2022 UNION SELECT customer_id, SUM(total_amount) FROM Orders2023 GROUP BY customer_id;
BSELECT customer_id, total_amount FROM Orders2022 UNION SELECT customer_id, total_amount FROM Orders2023;
CSELECT customer_id, SUM(total_amount) AS total_sales FROM (SELECT customer_id, total_amount FROM Orders2022 UNION ALL SELECT customer_id, total_amount FROM Orders2023) AS combined GROUP BY customer_id;
DSELECT customer_id, total_amount FROM Orders2022 JOIN Orders2023 ON Orders2022.customer_id = Orders2023.customer_id;
Step-by-Step Solution
Solution:
Step 1: Combine all rows
Use UNION ALL to include all rows from both tables including duplicates.
Step 2: Aggregate totals
Wrap combined results in a subquery and GROUP BY customer_id to sum total_amount.
Final Answer:
SELECT customer_id, SUM(total_amount) AS total_sales FROM (SELECT customer_id, total_amount FROM Orders2022 UNION ALL SELECT customer_id, total_amount FROM Orders2023) AS combined GROUP BY customer_id; -> Option C
Quick Check:
UNION ALL + GROUP BY sums totals correctly [OK]
Quick Trick:Use UNION ALL then GROUP BY to sum totals [OK]
Common Mistakes:
MISTAKES
Using UNION instead of UNION ALL (loses duplicates)
Not aggregating after UNION ALL
Trying to aggregate before UNION
Master "Set Operations" in SQL
9 interactive learning modes - each teaches the same concept differently
