SQL · Module 5

Answers and explanations · Summarise at a declared grain

Compare these references after your own attempt. You can read them at any time; they do not record a live model or vendor run.

Exercise 05a

The counts 2 cancelled, 11 completed and 1 pending sum to all 14 orders. No status filter belongs in this all-status question.

SELECT status,COUNT(*) AS orders FROM aabha_sql.orders GROUP BY status ORDER BY status;

Expected output:

status	orders
cancelled	2
completed	11
pending	1

Runnable answer: sql/solutions/05a.sql

Exercise 05b

Customers 1, 2, 3 and 4 each have at least two completed orders. WHERE removes noncompleted rows before HAVING checks the customer aggregate.

SELECT customer_id,COUNT(*) AS completed_orders FROM aabha_sql.orders
WHERE status='completed' GROUP BY customer_id HAVING COUNT(*)>=2 ORDER BY customer_id;

Expected output:

customer_id	completed_orders
1	2
2	3
3	2
4	2

Runnable answer: sql/solutions/05b.sql

Return to this module