GROUP BY and HAVING

Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into one number. GROUP BY says what the number is per: per status, per customer, per month.

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

-- status    orders
-- paid      11
-- pending   2
-- refunded  2

Every column in SELECT must either appear in GROUP BY or be inside an aggregate. SQLite lets you break this rule and quietly takes a value from some row in the group. Most databases refuse the query, and you shouldn't rely on either behaviour.

Three different counts. COUNT(*) counts rows. COUNT(city) counts rows where city isn't NULL. COUNT(DISTINCT customer_id) counts different values. The shop has 11 paid orders, but only 7 different customers placed them.

WHERE filters rows before grouping; HAVING filters groups after. Paid orders only is a condition on rows, so it goes in WHERE. Customers with at least two paid orders is a condition on a group's count, so it goes in HAVING.

SELECT customer_id, COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2;

-- customer_id  paid_orders
-- 1            3
-- 3            2
-- 4            2

Revenue is the money from items on paid orders: SUM(quantity * unit_price) over order_items, joined to orders so refunded and pending ones can be dropped. Use unit_price, the price actually paid, not the product's list price. Order 109 sold shoes at a discount.

Loading your workspace…