Grouping and Aggregation

Nobody wants two million order rows. They want revenue by month, or the five best-selling products, or how many customers ordered last quarter.

Aggregation turns many rows into one number. GROUP BY decides which rows get bundled together before that number is worked out.

-- No GROUP BY: the whole table is one group, so you get one row
SELECT
    COUNT(*)             AS order_count,
    SUM(shipping_fee)    AS total_shipping,
    MIN(order_date)      AS first_order,
    MAX(order_date)      AS latest_order
FROM orders;

-- With GROUP BY: one row per distinct status
SELECT
    status,
    COUNT(*)          AS order_count,
    SUM(shipping_fee) AS total_shipping
FROM orders
GROUP BY status
ORDER BY order_count DESC;

• An aggregate with no GROUP BY collapses everything to a single row. That is a group too — the group is "all rows".
• GROUP BY status creates one bucket per distinct status value, then computes the aggregates inside each bucket.
• Every column in the SELECT list must either be in the GROUP BY or be wrapped in an aggregate. There is a reason for that rule, and it is in the next section.

Group orders by status and one row in the result represents perhaps 40,000 orders. Now ask for order_date in that row. Which of the 40,000 dates should the database return?

There is no defensible answer, so the database refuses. The error usually reads "column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause", which is long but precise.

MySQL historically allowed this and returned an arbitrary value from the group. Recent versions reject it by default. If you meet a query relying on the old behaviour, treat the values it returns as unreliable rather than assuming they were chosen sensibly.

This is the distinction the lesson turns on, and the execution order from the SELECT lesson explains it completely.

WHERE runs before GROUP BY. At that point there are no groups yet — only individual rows — so WHERE decides which rows are allowed into the grouping.

HAVING runs after GROUP BY. By then the aggregates have been calculated, so HAVING can test them. "Customers with more than five orders" is a statement about a group, so it belongs in HAVING.

SELECT
    c.company_name,
    COUNT(DISTINCT o.order_id)            AS order_count,
    SUM(oi.quantity * oi.unit_price)      AS goods_value
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.order_date >= '2026-01-01'          -- filter rows first
  AND o.status <> 'cancelled'
GROUP BY c.company_name
HAVING SUM(oi.quantity * oi.unit_price) > 10000   -- then filter groups
ORDER BY goods_value DESC;

• WHERE removes 2025 orders and cancelled orders before any grouping happens, so they never reach the totals.
• HAVING then drops customers whose total came out below 10,000. That test needs the total, which only exists after grouping.
• COUNT(DISTINCT o.order_id) rather than COUNT(*) is the joins lesson showing up again: the join to order_items repeated each order once per line, so COUNT(*) would count lines and call them orders.
• SQL Server requires the full expression in HAVING rather than the alias, because HAVING is resolved before SELECT. PostgreSQL and MySQL permit the alias.

SELECT
    YEAR(o.order_date)                AS order_year,
    MONTH(o.order_date)               AS order_month,
    COUNT(DISTINCT o.order_id)        AS orders,
    COUNT(DISTINCT o.customer_id)     AS customers,
    SUM(oi.quantity * oi.unit_price)  AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY YEAR(o.order_date), MONTH(o.order_date)
ORDER BY order_year, order_month;

• You can group by an expression, not only a column. The same expression has to appear in both the GROUP BY and the SELECT list.
• Two DISTINCT counts from one query: how many orders, and how many different customers placed them. Those numbers answer different questions and are often confused in reports.
• One honest limitation: months with no orders at all do not appear, because there are no rows to group. Showing a zero for a quiet month needs a list of months to join against — a calendar table, or a generated series.