Aggregate Functions and GROUP BY

What are aggregate functions?

Aggregate functions summarise many rows into a single value.

Example

SELECT region,
SUM(total_amount) AS revenue,
COUNT(*) AS order_count,
AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY region
ORDER BY revenue DESC;

How it works

• GROUP BY creates one group per distinct value (here, per region).
• Aggregate functions calculate a result for each group.
• Every selected column that isn't aggregated generally must appear in GROUP BY (rules vary slightly by database).

Logical order in which SQL processes a query:

This explains why you usually can't use a SELECT alias inside WHERE: WHERE runs first.

HAVING versus WHERE

SELECT region, SUM(total_amount) AS revenue
FROM orders
WHERE order_date >= '2026-01-01' -- row filter
GROUP BY region
HAVING SUM(total_amount) > 10000; -- group filter

Common mistakes

• Wrong unit of analysis: averaging line items gives a different result from averaging orders.
• COUNT(column) vs COUNT(*)****: COUNT(column) excludes NULLs, while COUNT(*) counts all rows.
• Counting duplicates: use COUNT(DISTINCT customer_id) for unique customers.
• Filtering aggregates with WHERE: WHERE SUM(...) > 100 causes an error. Use HAVING.
• Integer division: in some systems (PostgreSQL, SQL Server), 5 / 2 = 2. Multiply by 1.0 for decimals.

Try it: Calculate monthly revenue and order count. Then show only months with more than 100 orders.