SQL Querying

Grouping and Aggregation

Grouping partitions qualifying rows by key and computes one result per group. WHERE decides which detail rows participate, while HAVING decides which completed groups survive.

Without GROUP BY, an aggregate query treats all qualifying rows as one group.
Every nonaggregated select expression must be determined by the grouping key; portable SQL lists those expressions in GROUP BY.
WHERE and HAVING answer different questions.
Most aggregates ignore null, but COUNT(*) counts rows.
Joining multiple one-to-many relationships before aggregation can multiply measures and produce plausible but incorrect totals.
DISTINCT inside an aggregate changes its input bag and should encode a real rule, not compensate for an incorrect join.

Rows first, groups second

Paid revenue by customer

SELECT o.customer_id,
       COUNT(DISTINCT o.order_id) AS paid_orders,
       SUM(i.quantity * i.unit_price) AS paid_revenue
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'PAID'
GROUP BY o.customer_id
HAVING SUM(i.quantity * i.unit_price) >= 1000
ORDER BY paid_revenue DESC, o.customer_id;

Worked evaluation for two customer groups
Stage	Customer 7	Customer 8
Joined lines	Paid lines 600 and 500; cancelled line 900	Paid lines 400 and 300
After `WHERE`	600 and 500	400 and 300
Grouped aggregate	Revenue 1100	Revenue 700
After `HAVING`	Kept	Removed

The cancelled 900 line never enters customer 7's group because WHERE acts first. Customer 8 forms a valid group and gets a 700 total, but HAVING removes that whole group afterward. Moving the revenue condition to WHERE would be both temporally and semantically wrong.