MySQL / Relational foundations and precise SQL / Lesson 4

Aggregation and questions inside questions

Summarize the correct population and compare groups without losing the meaning of missing values.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Distinguish row filtering from group filtering.
Use COUNT, SUM, correlated subqueries, and ONLY_FULL_GROUP_BY correctly.

Choose the population before grouping

Use the unchanged fixture. Aggregation collapses many rows into one result per group. WHERE filters input rows; GROUP BY forms groups; HAVING filters grouped results. A report of paid revenue must filter paid orders before summing. Adding unpaid rows and then checking whether a group contains a paid row answers a different question and overstates revenue.

Count rows or known values

COUNT(*) counts rows, while COUNT(expression) counts non-NULL expression values. Most aggregates ignore NULL input values. SUM over an empty set is NULL, not zero, so use COALESCE when zero genuinely means no matching revenue. In a left join, COUNT(o.id) counts matched orders; COUNT(*) also counts the preserved customer row with no order.

sql Customer summaries including zero orders

SELECT c.id, COUNT(o.id) AS order_count,
 COALESCE(SUM(o.total),0) AS ordered_total
FROM customers c LEFT JOIN orders o ON o.customer_id=c.id
GROUP BY c.id ORDER BY c.id;

Expected output

id | order_count | ordered_total
1 | 2 | 55.00
2 | 1 | 10.00
3 | 0 | 0.00

Amina has two orders totaling 55.00, Leo one totaling 10.00, and Noor none totaling 0.00. The NULL-extended row contributes nothing to COUNT(o.id). Replacing it with COUNT(*) incorrectly reports one order for Noor.

Make every selected value unambiguous

MySQL 8.4 enables ONLY_FULL_GROUP_BY by default. A selected nonaggregate must be grouped or functionally dependent on grouping keys. Customer name is dependent on a grouped customer primary key, but an arbitrary order status is not dependent on customer_id. Disabling the mode hides the ambiguity. ANY_VALUE expresses indifference; it does not identify the latest or largest associated row.

Use a subquery for comparison

A scalar subquery supplies exactly one column and at most one row; an aggregate without GROUP BY supplies a single result even for empty input. Correlated subqueries refer to the outer row, whereas independent subqueries compute a shared comparison. Understand the result shape before selecting operators: equality requires a scalar, IN accepts a value set, and EXISTS asks only whether a row exists.

sql Orders above the overall average

SELECT id, total FROM orders
WHERE total > (SELECT AVG(total) FROM orders)
ORDER BY id;

Expected output

id | total
101 | 25.00
102 | 30.00

The three totals sum to 65.00, so their average is approximately 21.666667. Orders 101 and 102 exceed it; 103 does not. The exact number of displayed fractional digits depends on expression metadata, so the result below lists only the selected exact totals.

Reconcile independent summaries

To audit an order, first group order_items by order_id and sum quantity * unit_price, then compare that subtotal with orders.total. Do not sum order totals after expanding them into lines. Decide how missing lines should behave: a left join reveals them, and an explicit NULL test distinguishes missing detail from a real zero-valued order. Reporting correctness depends as much on these definitions as on SQL syntax.

Common errors and fixes

ERROR 1055 (42000) with ONLY_FULL_GROUP_BY.

Why it happens: A customer group selects order status despite several possible statuses.
How to fix it: Group at the required status grain or explicitly aggregate the intended fact.
Prevent it next time: Describe what each output column means within one group.

ERROR 1111 (HY000): Invalid use of group function.

Why it happens: COUNT(*) was placed in WHERE, which filters before grouping.
How to fix it: Use HAVING COUNT(*) >= 2 for group-level conditions.
Prevent it next time: Separate input-row predicates from aggregate-result predicates.

ERROR 1242 (21000): Subquery returns more than 1 row.

Why it happens: A scalar comparison receives multiple order totals.
How to fix it: Choose a justified aggregate or use a set operator matching the question.
Prevent it next time: Predict the subquery column count and row cardinality first.

Check your understanding

1. Find customers with at least two orders.

Show worked solution

SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id HAVING COUNT(*) >= 2 ORDER BY customer_id returns 1/2. The grouping forms two customer groups, and HAVING removes Leo's single-order group. Noor never enters because the source is orders.

2. Compute paid revenue and explain why Amina's new order is excluded.