TopolearnPer-customer totals, per-day counts and per-category averages are all GROUP BY queries.
GROUP BY splits the rows into groups that share the same values in the listed columns, then computes the aggregates once per group:
SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
FROM orders
GROUP BY customer_id;The rule that follows: every column in the select list must either appear in GROUP BY or be inside an aggregate. Selecting status here would be ambiguous, since a customer has orders with several statuses. PostgreSQL relaxes this slightly: if you group by a table's primary key, you may select that table's other columns, because they are determined by the key. MySQL with ONLY_FULL_GROUP_BY disabled lets you select anything and returns an arbitrary value, which hides bugs.
WHERE and HAVING both filter, at different moments:
WHEREfilters rows before grouping. It cannot use aggregates.HAVINGfilters groups after aggregation, so it can:HAVING SUM(total) > 200.
The logical evaluation order makes this clear: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. A condition on a plain column belongs in WHERE, where it reduces the work early; put only aggregate conditions in HAVING.
You can group by expressions, such as date_trunc('month', ordered_at) to get monthly figures. NULLs form their own group: all customers with an unknown city land in one NULL group.
A group exists only if at least one row falls into it. A per-customer report built from orders alone will never show Dev, who has no orders; to include zero rows you need an outer join, coming up in the Joins module.
Example
SELECT customer_id,
COUNT(*) AS orders,
SUM(total) AS spent
FROM orders
WHERE status <> 'cancelled' -- row filter, before grouping
GROUP BY customer_id
HAVING SUM(total) > 200 -- group filter, after aggregation
ORDER BY spent DESC;
-- customer_id | orders | spent
-- 1 | 2 | 257.50
-- 2 | 2 | 247.99
SELECT date_trunc('month', ordered_at) AS month, COUNT(*)
FROM orders
GROUP BY month
ORDER BY month;
-- 2026-01-01 00:00:00+00 | 1
-- 2026-02-01 00:00:00+00 | 2
-- 2026-03-01 00:00:00+00 | 2Remember
- Each selected column must be grouped or aggregated
- WHERE filters rows before grouping; HAVING filters groups after
- Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
- Groups only exist for values present in the data
Watch out for
- Putting non-aggregate conditions in HAVING instead of WHERE, which works but filters late and reads oddly.
- Expecting groups with zero rows to appear in the result.