Aggregations, GROUP BY and HAVING

Aggregate functions

An aggregate function turns many rows into one value:

Revenue at line level is quantity × unit_price × (1 − discount_pct). Total 2025 net revenue from completed orders requires the order status, which lives in orders — so this preview uses a filter on order_items only to show the functions:

SELECT COUNT(*)                 AS order_lines,
       COUNT(DISTINCT order_id) AS orders,
       SUM(quantity)            AS units,
       ROUND(AVG(unit_price), 2) AS avg_unit_price,
       MIN(unit_price)          AS cheapest_line_price,
       MAX(unit_price)          AS most_expensive_line_price
FROM order_items;

GROUP BY: one result row per group

GROUP BY splits rows into groups that share the same values and computes aggregates per group:

SELECT status,
       COUNT(*) AS orders,
       ROUND(AVG(discount_pct) * 100, 1) AS avg_discount_pct
FROM orders
GROUP BY status
ORDER BY orders DESC;

Group by several columns for a breakdown:

SELECT substr(order_date, 1, 4) AS year,
       channel,
       COUNT(*) AS orders
FROM orders
WHERE status = 'completed'
GROUP BY substr(order_date, 1, 4), channel
ORDER BY year, channel;

substr(order_date, 1, 4) takes the first four characters of the ISO date — the year. (The date-functions lesson shows the proper date functions in each dialect.)

The golden rule

Every column in SELECT must either be in the GROUP BY or inside an aggregate. SELECT channel, store_id, COUNT(*) … GROUP BY channel is an error in PostgreSQL and SQL Server, because there are many store_ids per channel and the database cannot know which one to show. (SQLite and MySQL in some modes silently pick an arbitrary value — a dangerous source of wrong reports.)

HAVING: filter groups

WHERE filters rows before grouping; HAVING filters groups after aggregation:

SELECT customer_id,
       COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'          -- row filter
GROUP BY customer_id
HAVING COUNT(*) >= 40               -- group filter
ORDER BY completed_orders DESC;

Read it as: among completed orders, count orders per customer, keep customers with at least 40, sort.

Worked example: revenue by category

Revenue needs columns from order_items (quantity, price, discount) and products (category) — that requires a join, the topic of the next chapter. As a preview of how aggregation and joins combine:

SELECT p.category,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)), 0) AS net_revenue,
       SUM(oi.quantity) AS units
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY net_revenue DESC;

Interpretation: Electronics and Home & Kitchen generate most revenue even though Grocery sells many units at low prices — volume and value tell different stories. (This query still includes cancelled and returned orders; the joins lesson fixes that.)

Common mistakes

Practice

Summary

• Aggregates collapse rows: COUNT, SUM, AVG, MIN, MAX, COUNT(DISTINCT …).
• GROUP BY produces one row per group; every selected column is grouped or aggregated.
• WHERE filters rows before grouping; HAVING filters groups after.
• Beware averages of averages and counting rows after joins.