Summarizing Data · GROUP BY two columns
4. Orders by customer and status
Concept: GROUP BY two columnsshow
GROUP BY can take more than one column. GROUP BY a, b makes one group for every combination of a and b that appears in the data, and the aggregates are worked out per combination. Every column in SELECT that isn't inside COUNT() or SUM() must also be listed in GROUP BY.
Example (different data):
SELECT store, payment_type, COUNT(*) AS sales FROM receipts GROUP BY store, payment_type ORDER BY store, payment_type;
Your task
Support wants to see how each customer's orders split by status, so they can spot people with refunds or orders stuck in pending.
Return customer_id, status, order_count (number of orders) and total_amount (sum of amount) for every combination of customer and status that has orders. Sort by customer_id, then status A to Z.
Solution unlocks after 3 more tries
Starting Postgres in your browser…
query.sql
Output
Write a query and press Run Query (Ctrl/⌘ + Enter).