Aggregation, Windows and Useful Reports

19.1.1 PLAIN — in simple words#

1. Grouping collects rows that share selected values so an aggregate can describe each collection. A line table can become one total per order, or one quantity per product, depending on the chosen grouping.
2. The output row now means something different. A row that previously represented one line may now represent an entire order’s calculated total.
3. Say which input records qualify before grouping. A report for accepted orders, all drafts or only delivered items uses different populations even if the same amount expression appears in each query.

19.1.2 PLAIN — a picture in your head#

1. Mira sorts slips into envelopes by order number, then adds the amounts in each envelope. Sorting the same slips by product instead creates different envelopes and different useful answers.
2. A summary envelope is not another original sale. It is a derived description of the slips selected for it.
3. Where the comparison breaks: SQL grouping follows comparison and NULL rules, not a clerk’s judgement. It can group missing values together without proving that the underlying unknown real-world values are the same.

19.1.3 PLAIN — a worked example#

SELECT order_id,
       COUNT(*) AS line_count,
       SUM(quantity) AS unit_count,
       SUM(quantity * unit_price_minor) AS total_minor
FROM order_lines
GROUP BY order_id
ORDER BY order_id;

1. O-1042 produces two lines, three units and 17,100 paise. O-1043 produces two lines, three units and 11,550 paise.
2. Grouping instead by product_id gives P-NOTE three units and 22,650 paise; P-PEN three units and 6,000 paise.
3. Both sets reconcile to six units and 28,650 paise. They answer different questions while accounting for the same four line contributions.

19.1.4 PLAIN — what is really happening inside#

1. The grouping keys determine which rows share an aggregate state. SUM accumulates values; COUNT increments under its defined condition; other aggregates maintain different working state.
2. A WHERE condition selects input rows. HAVING selects groups after aggregation according to group-level conditions. Moving a threshold between them can change the answer.
3. Selecting an arbitrary ungrouped description beside a grouped total can make a report ambiguous or engine-dependent. Join a clearly identified description at the correct grain or include the appropriate grouping key.

19.1.5 TECHNICAL — the engineer’s version#

1. Define input grain, qualification and output grain in the report contract. Grouping and aggregation do not establish that the input has no accidental join fan-out; that must be checked separately. [S58] [S19]
2. SQL grouping treats NULL values as belonging to the same grouping category for this purpose. That grouping convention does not assert equality of unknown business identities.
3. The physical engine may aggregate with sorting, hashing or another supported strategy. The query’s grouping semantics are distinct from the selected implementation.

19.1.6 WORDS — remember these#

1. Grouping key: the fields defining each collection — expressions whose equal grouping values identify one aggregate group. Report grain: what one result row describes — the precise unit represented after selection, joining and aggregation. Aggregate state: information accumulated for a group — working values sufficient to compute an aggregate such as a sum and count.

19.2.1 PLAIN — in simple words#

1. A count needs a noun: rows, known prices, distinct customers or physical units. Those quantities are not interchangeable.
2. An average needs a denominator. Average order value divides order amount by orders; average price per unit divides amount by units. Averaging line prices gives each line equal weight, not each unit.
3. Missing values must not silently become zero. A zero price can be a known free item; an unknown proposed price is a different state.

19.2.2 PLAIN — a picture in your head#

1. Two boxes contain one pen and nine notebooks. Averaging the two box labels gives each box equal influence. Averaging the ten individual items gives the larger box nine times the influence.
2. Neither calculation is mysterious once you say what is being averaged.
3. Where the comparison breaks: a SQL aggregate’s NULL behaviour is a defined rule, not a human decision to exclude an incomplete observation. The report author must explain whether that exclusion matches the intended metric.

19.2.3 PLAIN — a worked example#

1. The canonical mean order amount is 28,650 / 2 = 14,325 paise. The mean line amount is 28,650 / 4 = 7,162.5 paise. The quantity-weighted mean unit price is 28,650 / 6 = 4,775 paise.
2. These are three different metrics. Their fractional results need a display-rounding policy; calculating an average does not create a half-paise payable obligation.
3. In a separate two-line example, buy one pen at 2,000 and nine notebooks at 7,550. The unweighted average of line unit prices is 4,775, but the unit-weighted mean is (2,000 + 9×7,550)/10 = 6,995 paise.
4. In the draft D-SQL-01, one proposed price is NULL. A SUM over known line amounts does not become a complete quote merely because the database returns a number. Report the missing-price count alongside any known subtotal.

19.2.4 PLAIN — what is really happening inside#

1. COUNT(*) counts input rows. COUNT(expression) counts non-null expression results. SUM and AVG generally ignore NULL arguments in these documented SQL implementations, so missingness can change a denominator. [S19]
2. An empty aggregate input needs explicit interpretation. COUNT is zero; SUM can be NULL. COALESCE can present a chosen zero, but the application must decide whether “no observations” and “observed total zero” are equivalent for this report.
3. Combining subgroup averages requires their weights. A branch with one order and a branch with 999 orders must not receive equal weight when calculating the average order value across all 1,000 orders.

19.2.5 TECHNICAL — the engineer’s version#

1. A weighted mean is Σ(w_i x_i) / Σw_i for the defined included observations and valid weights. Guard the zero-denominator case and state whether zero, NULL or a labelled “not available” result is intended.
2. Preserve sufficient statistics for recombination. Sum and count can combine to recover an overall mean; an unweighted average of subgroup means generally cannot.
3. Integer division and numeric result types differ across engines and expressions. Use an explicit numeric conversion where fractional results are intended, and distinguish exact arithmetic from floating-point approximation. [S21] [S80]

19.2.6 WORDS — remember these#

1. Denominator: what the total is divided by — the explicitly defined count or weight underlying a ratio. Weighted mean: larger contributions receive proportionate influence — the sum of value-times-weight divided by total valid weight. Known subtotal: a total over available values only — a partial calculation that must not be labelled complete when required observations are missing.

19.3.1 PLAIN — in simple words#

1. Grouping normally collapses several detail rows into one result per group. A window function can calculate across related rows while keeping each detail row visible.
2. This is useful for running totals, rankings and comparisons with a previous observation. The calculation needs a partition, an ordering where relevant, and sometimes an explicit frame describing which neighbouring rows contribute.
3. The order used inside a window calculation does not automatically promise the final display order. Keep the final ORDER BY as well.

19.3.2 PLAIN — a picture in your head#

1. Instead of putting slips into one summary envelope, write a running subtotal beside each slip while leaving every slip on the page.
2. A fresh running total can begin for each order, just as a new column of arithmetic starts on a new receipt.
3. Where the comparison breaks: a window frame can include peers with equal ordering values or a selected range of rows. It is not always simply “everything printed above this line.”

19.3.3 PLAIN — a worked example#

SELECT order_id, line_no,
       quantity * unit_price_minor AS line_minor,
       SUM(quantity * unit_price_minor) OVER (
         PARTITION BY order_id ORDER BY line_no
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_minor
FROM order_lines
ORDER BY order_id, line_no;

1. For O-1042, the running amounts are 15,100 and 17,100. For O-1043, they are 7,550 and 11,550. Four detail rows remain.
2. In a separate ordered population with values 100, 100 and 250, a row-by-row SUM with a unique tie-breaker gives 100, 200 and 450. A peer-inclusive range frame ordered only by value can give 200, 200 and 450.
3. The different answers follow different frames. Neither should be called an arithmetic failure when the intended frame was not specified.

19.3.4 PLAIN — what is really happening inside#

1. PARTITION BY separates independent window calculations. ORDER BY inside OVER arranges rows for order-sensitive functions. The frame then identifies the subset used by frame-sensitive calculations.
2. ROW_NUMBER assigns positions; RANK gives tied peers the same rank and leaves gaps; DENSE_RANK gives tied peers the same rank without those gaps. On 100,100,250 in ascending order, ranks are 1,1,3 and dense ranks 1,1,2.
3. LAST_VALUE can surprise readers because its default frame may end at the current peer group rather than the partition’s last row. Request the whole-partition frame when that is actually the intended question. [S110]

19.3.5 TECHNICAL — the engineer’s version#

1. Window partition, ordering and frame are distinct clauses. ROWS, RANGE and supported GROUPS frames count or interpret boundaries differently; peers are rows equal under the window ordering. [S97]
2. A stable row-by-row running total needs an ordering that distinguishes ties, such as (event_time, event_id) when that identifier supplies the intended deterministic order. Determinism does not prove that this tie-break is causal order.
3. Windows operate over the query’s relevant result stage, not automatically over raw base rows. A common table expression can make a prior grouping stage explicit before applying a window to grouped totals. [S96] [S116]

19.3.6 WORDS — remember these#

1. Window function: calculate across related rows without collapsing each one — an SQL function evaluated over a partition and, where applicable, an ordered frame. Peer group: rows tied under a window’s ordering — entries equal on all ordering expressions used to define peers. Window frame: the contributing region for one current row — a bounded subset of its partition under ROWS, RANGE or another supported frame mode.

19.4.1 PLAIN — in simple words#

1. A daily report needs a definition of “day.” The event’s time, the system’s recording time and the business’s reporting timezone can produce different groupings.
2. A late-arriving record can describe yesterday’s event. Decide whether to revise yesterday’s report, show an adjustment today or use another explicit policy.
3. Adjacent periods need boundaries that do not double-count events exactly at midnight. A half-open interval includes its start and excludes its end.

19.4.2 PLAIN — a picture in your head#

1. Mira places slips into dated trays according to when the sale happened, not necessarily when Dev carried the slip to the desk.
2. A tray labelled “Tuesday” is ambiguous unless everyone agrees which clock and calendar define Tuesday.
3. Where the comparison breaks: timezones can have changing offsets and daylight-saving transitions. A local calendar day need not equal exactly 24 hours of elapsed time. Calendar grouping and duration measurement are different tasks.

19.4.3 PLAIN — a worked example#

1. In a separate UTC exercise, event E1 occurs at 2026-09-01T23:59:59Z; E2 occurs at 2026-09-02T00:00:00Z. The interval from September 1 midnight inclusive to September 2 midnight exclusive includes E1 but not E2.
2. The next daily interval includes E2. The shared boundary creates no duplicate contribution.
3. If E1 is recorded after E2, an event-time report still places it on September 1. A recording-time report can place it on September 2. Both need labels that make the distinction visible.
4. These invented timestamps are not dates assigned to the canonical orders, whose earlier fixture did not define a complete business-time reporting policy.

19.4.4 PLAIN — what is really happening inside#

1. Parse and validate timestamp representations before grouping. Text prefixes only behave as reliable date keys when the representation and timezone policy make that interpretation valid.
2. Preserve the original instant and relevant source context when needed. A display conversion should not silently replace the stored event time with a server-local assumption.
3. Time buckets also need an inclusion policy for missing or invalid times. Dropping those records without a count can make a daily dashboard appear more complete than its evidence.

19.4.5 TECHNICAL — the engineer’s version#

1. Specify event-time versus processing-time semantics, timezone or UTC convention, interval boundaries and late-correction policy. Chapter 41 develops these requirements for streaming systems.
2. Prefer explicit >= start AND < end boundaries for adjacent instant ranges when that matches the metric. A local calendar boundary should be converted with the appropriate timezone rules rather than assuming every day is a fixed number of seconds.
3. Retain report version or as-of metadata when later records can change an earlier period. A reproducible historical report needs its source snapshot and transformation version, not just a date label.

19.4.6 WORDS — remember these#

1. Time bucket: the period a record contributes to — a reporting interval defined by clock semantics, timezone and boundary rules. As-of time: when the available information was evaluated — the observation or snapshot boundary attached to a report. Late arrival: a record received after its event period — an observation whose processing time occurs later than the event-time window it describes.

19.5.1 PLAIN — in simple words#

1. A distinct count needs an identity rule. Counting distinct display names does not necessarily count people, and counting distinct prices does not count sale lines.
2. Repeated delivery of one event and two separate events with equal values must be treated differently. The difference comes from event identity and policy, not visual similarity.
3. A deduplicated report should say what was considered the same and what happened to conflicts. Silent removal can hide a data-quality problem.

19.5.2 PLAIN — a picture in your head#

1. Two photocopies of one receipt should not create two sales. Two genuine receipts with the same amount should still count twice.
2. The receipt’s identity and provenance distinguish those cases; the amount alone does not.
3. Where the comparison breaks: a real system can receive conflicting payloads under one identifier. Choosing the first or last without a defined rule can hide corruption or policy disagreement. Deduplication is not merely deleting identical-looking paper.

19.5.3 PLAIN — a worked example#

1. The separate O-REPEAT fixture has two different line numbers with the same notebook amount, 7,550 each. Counting distinct amounts gives one value; counting lines gives two; summing amounts gives 15,100.
2. A redelivered copy of (O-REPEAT,1) with identical payload is different from line 2. Under a declared input policy, the repeated line key can be recognised as a replay while line 2 remains a separate contribution.
3. If the replay changes quantity, it is a conflicting same-key observation, not an ordinary duplicate. The earlier import and event-consumer policies must be respected rather than replaced by a new “last one wins” rule.

19.5.4 PLAIN — what is really happening inside#

1. SQL DISTINCT applies to the selected expressions. Removing duplicate projected values can collapse records that differ on unselected identity fields.
2. Distinct aggregation also has NULL rules and resource costs. Exact distinct counting may need to retain or sort many keys. Approximate counting structures answer a different question with an error model that must be disclosed.
3. Combining distinct counts across groups by addition can double-count identities appearing in more than one group. Preserve the union or a suitable reviewed mergeable representation when the metric is overall distinct identity.

19.5.5 TECHNICAL — the engineer’s version#

1. Define the deduplication key, scope, payload-equivalence rule, conflict disposition and retention horizon. A SQL DISTINCT operation is only one possible implementation step, not the full policy. [S57]
2. COUNT(DISTINCT expression) counts distinct non-null expression values in the documented aggregate semantics. It is not a universal distinct-person estimator. [S19]
3. Exact totals, exact distinct counts and approximate sketches have different composability properties. Never merge subgroup metrics without verifying the algebra required by that metric.

19.5.6 WORDS — remember these#

1. Distinct count: count different values under a rule — cardinality of the included value set after the specified equality and NULL treatment. Deduplication key: what identifies one contribution — the scoped fields used to recognise a replay rather than a separate event. Mergeable summary: subgroup evidence can be combined correctly — a summary with a defined merge operation preserving the intended metric or error model.

19.6.1 PLAIN — in simple words#

1. Reconciliation checks that a report accounts for the intended source records and explains differences. It is more than comparing one grand total.
2. Count input records, accepted contributions, exclusions, duplicates and conflicts. Compare identities and amounts at useful intermediate levels.
3. A total can match by accident when one missing amount cancels one duplicated amount. The report needs enough traceability to detect that cancellation.

19.6.2 PLAIN — a picture in your head#

1. Mira checks both the total money and the list of receipts placed in the envelope. The right amount with the wrong receipts is not a completed reconciliation.
2. An exclusion note belongs beside the envelope, not in an undocumented memory of why some slips disappeared.
3. Where the comparison breaks: a database report can involve snapshots, transformations and late corrections across several stores. A matching receipt list at one stage does not establish the whole pipeline’s completeness.

19.6.3 PLAIN — a worked example#

1. For the canonical report, expected line identities are (O-1042,1), (O-1042,2), (O-1043,1) and (O-1043,2). Expected counts are four lines, two orders and six units.
2. Expected order totals are 17,100 and 11,550; expected product totals are 22,650 and 6,000. Both routes sum to 28,650.
3. Add an explicitly separate empty-order fixture. A report sourced only from order lines still has no row for that order. A report sourced from all orders with a left join can include it, with a chosen zero-line interpretation. The denominator must say which report is intended.
4. A useful report header therefore includes population, period, currency, missing-value treatment, source snapshot and transformation version before presenting a large number.

19.6.4 PLAIN — what is really happening inside#

1. Reconciliation compares a defined expected set with the observed contributions, then classifies differences. Missing, extra, duplicated and changed contributions require different explanations.
2. Checks at several grains help locate a defect: overall amount, order totals, product quantities and original line identities. Agreement at one grain cannot substitute for all the others.
3. Save the evidence needed to reproduce the result without exposing unnecessary personal data. A diagnostic report should not become an uncontrolled copy of every sensitive field.

19.6.5 TECHNICAL — the engineer’s version#

1. A reconciliation contract specifies source snapshot, contribution identity, inclusion predicate, units, arithmetic, grouping and tolerances where appropriate. Exact integer-paise examples use exact equality; uncertain measurements may require a separately justified tolerance.
2. Treat report generation as a versioned transformation. Reproducing a number requires both the relevant source state and the transformation’s semantics, not merely a saved SQL filename.
3. These educational checks validate the stated fixture and exercises. They do not certify live financial accounts, legal reporting obligations or an organisation’s complete data lineage.

19.6.6 WORDS — remember these#

1. Contribution identity: which source fact a result uses — a stable scoped identifier for a counted or summed input contribution. Exclusion register: explain what did not contribute — recorded counts and reasons for records outside the report’s accepted population. Transformation version: identify the calculation rules used — a versioned definition of parsing, filtering, joining and aggregation semantics.