Drill 4 of 5 in Summarizing Data
0

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).