Aggregation multiplicity

Aggregation multiplicity tells FeatureQL how many times a value should count when an aggregate collapses rows. The compiler works out how often each value occurs; you write PER only when a value repeats and the aggregate must be told how to count it.

Why multiplicity needs an explicit choice

A feature normally occurs once at its own entity grain. UNNEST(), FOR CROSS, RELATED(), and multi-entity bindings can repeat that feature across several rows.

Consider a customer lifetime value repeated beside every customer tag. After unnesting tags, should the value count once for the customer or once for every tag occurrence? Both answers can be valid, so the compiler cannot pick one for you.

Choose one contribution per key or per row

Use PER key when a value belongs to a business key. Use PER ROW when every occurrence should contribute:

DEFINE
    customers := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    customer_facts := INLINE_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        customer_ltv BIGINT,
        tags ARRAY(VARCHAR)
        FROM JSON([
            {"customer_id": 1, "customer_ltv": 100, "tags": ["x", "x", "y"]},
            {"customer_id": 2, "customer_ltv": 50, "tags": ["x"]}
        ])
    ),
    customer_ltv := customer_facts[customer_ltv],
    customer_tags := customer_facts[tags],
    t := UNNEST(ZIP(customer_tags AS tag))
RETURN
    tag := t[tag],
    by_customer := SUM(customer_ltv) PER customer_id GROUP BY (tag),
    by_occurrence := SUM(customer_ltv) PER ROW GROUP BY (tag)
FOR
    customer_id := BIND_VALUES(ARRAY(1, 2))
ORDER BY tag
;

Both results use the same unnested rows. For tag x:

• PER CUSTOMER_ID contributes 100 + 50 = 150;
• PER ROW contributes 100 + 100 + 50 = 250.

PER CUSTOMER_ID deduplicates (CUSTOMER_ID, value) pairs inside each output group. It is not SUM(DISTINCT value): two customers with the same value both contribute.

How the compiler knows a value repeats

Every value carries an occurrence: how often it is produced relative to the rows the query is evaluating. The compiler assigns it from the shape of the formula, so there is nothing to declare:

When a formula combines several values, the result keeps the coarsest occurrence. ORDER_TOTAL + SHIPPING, where both occur once per order, still occurs once per order. ORDER_TOTAL * 1.2 occurs once per order, because the literal is neutral.

An aggregate such as SUM() compares that occurrence with the rows it is collapsing. When the value occurs once per current row, the sum is safe. When the value occurs once per order but the aggregate collapses lines, the value repeats, and the compiler asks you to choose with PER.

Derived expressions

Multiplying or subtracting changes what a value is a function of. ORDER_TOTAL is a function of the order. ORDER_TOTAL * QTY is not: every line has its own quantity, so the product takes a different value on each line of the same order. It still owes one contribution per order, but there is no longer one value per order to deduplicate.

For such an expression PER ORDER_ID would be wrong, and the compiler refuses it. PER ROW is the repair:

DEFINE
    lines := ENTITY(),
    orders := ENTITY(),
    line_id := INPUT(BIGINT#lines),
    l := INLINE_COLUMNS(
        line_id BIGINT#lines BIND TO line_id,
        order_id BIGINT#orders,
        qty BIGINT,
        order_total BIGINT DETERMINED BY (ORDER_ID)
        FROM CSV(
            line_id,order_id,qty,order_total
            1,10,2,30
            2,10,1,30
            3,20,4,20
        )
    ),
    order_id := l[order_id],
    order_total := l[order_total],
    qty := l[qty]
RETURN
    booked := SUM(order_total) PER order_id,
    weighted := SUM(order_total * qty) PER ROW
FOR
    line_id := BIND_VALUES(ARRAY(1, 2, 3))
;

BOOKED counts each order total once. WEIGHTED counts every line, because that is what the product means. The error message for a bare SUM(ORDER_TOTAL * QTY) proposes PER ROW directly; it never proposes PER ORDER_ID for a row-dependent product.

Scalars and global totals do not change the picture. ORDER_TOTAL / GRAND_TOTAL, where GRAND_TOTAL is an ungrouped SUM, is still a function of the order and still takes PER ORDER_ID.

Use a key the value is a function of

PER K is accepted only when the compiler can see that the value is a function of K:

• a declared DETERMINED BY (K);
• the GROUP BY keys of an aggregate;
• the partition key of a window aggregate without a frame;
• the parent key of a RELATED() aggregate, even when the child foreign key carries no entity annotation.

Any other key is rejected with SUMMARIZABILITY-PER-KEY-INVALID, including a key that is finer than the value's occurrence. PER changes contribution inside the aggregate; it never asserts that the source data is unique.

When the source contract records DETERMINED BY (ORDER_ID), a multiplicity-sensitive aggregate still needs PER ORDER_ID if finer rows repeat the value. The determinant explains why repetition is expected; PER states how this aggregation handles it.

Where an aggregate over a group value runs

When an aggregate reads another aggregate, such as SUM(N1) GROUP BY (K) with N1 := SUM(ID) GROUP BY (G), it has to run on some set of rows. You never choose that set. The compiler derives it from the aggregate's own definition, following a few principles in priority order:

1. Purity. A feature's value depends only on its own definition and the bound data, never on the other features in the query.
2. Determinism. No result depends on an arbitrary choice, such as row order or which duplicate is kept.
3. Features are functions on a domain. A group value lives on its own rows (one row per G), not as a column copied onto the input rows.
4. Refuse rather than guess. When there is no single well-defined answer, the query is rejected.
5. SQL familiarity breaks ties, and only ties.

The placement rule follows from the first and third principles. The candidates are the input rows plus the rows of each group value the aggregate reads. The aggregate runs on the coarsest candidate where everything it reads is available: its operand, its FILTER, its GROUP BY keys and its PER key. With ID in 1..6 and G := ID % 2, N1 is 12 and 9:

• SUM(N1) PER ROW reads only N1, so it runs on the two G rows: 21.
• SUM(N1 + ID * 0) PER ROW also reads ID, which only exists on input rows: 63. The same happens when a FILTER reads ID.

The number changes because the question changed: "once per group" versus "once per input row". PER key removes the ambiguity. SUM(N1) PER G gives 21 wherever it runs.

Two restrictions only ever reject, never change a number. A GROUP BY result is not broadcast onto finer output rows (use OVER (...) for that). Errors about the output shape are reported before errors inside one aggregate, because fixing the shape can change the inner error.

NULL keys contribute nothing to PER

PER key ignores rows whose key is NULL, exactly like COUNT(DISTINCT key). COUNT(PER key) and COUNT(DISTINCT key) therefore always agree. The alternatives break the principles. Counting each NULL row as its own key makes the result depend on the row set the aggregate happens to run on. Merging NULLs into one key forces an arbitrary pick when those rows carry different values.

GROUP BY still keeps its NULL group; only PER drops it. To count unknown keys, COALESCE the key before the GROUP BY that builds the value, then aggregate PER the coalesced key. Watch for keys that come from a lookup: a RELATED() miss yields a NULL key, and that row silently leaves a PER sum.

The full rule set, a prediction procedure and the rejected alternatives are in the assistant help corpus under help('aggregate_placement').

Trust the suggested repair

When an aggregate is rejected, the message names a PER form that parses and type-checks in that exact place: SUM(ORDER_TOTAL PER ORDER_ID), SUM(ORDER_TOTAL * QTY PER ROW), or SUM(SPENT PER CUSTOMER_ID). Copy it rather than guessing a key. If the suggestion is not what you meant, the other legal form is always PER ROW.

Know when PER is not needed

PER is only relevant when all of these are true:

• the aggregate is sensitive to repeated input rows (SUM(), AVG(), COUNT(PER key); MIN(), MAX(), and COUNT(DISTINCT …) are not);
• the consumed value occurs less often than the rows being collapsed;
• nothing in the query already proves that each value appears once.

That last point matters. When the FOR clause binds entity-annotated keys and the value is a function of exactly those keys, each spine row carries the value once, and a bare SUM is accepted. A customer rollup consumed on a customer spine needs no PER.

Window aggregates with OVER (...) preserve the current rows and are the sanctioned broadcast form; they do not use PER. PER answers “how many times does this value contribute when rows collapse”, and a window does not collapse rows. Window additivity is a separate check: SUM(rate) OVER () of a NOT ADDITIVE rate is still rejected.

Keep multiplicity and additivity separate

Multiplicity asks how many times the current value contributes. Additivity asks whether combining that value across removed axes preserves its meaning.

• ORDER_TOTAL DETERMINED BY (ORDER_ID) may be additive across orders but repeated on line rows. It needs PER ORDER_ID.
• DAILY_STOCK NOT ADDITIVE ACROSS (DAY_ID) may appear exactly once per product and day. It still cannot be summed when day disappears.
• A repeated daily stock value can violate both contracts and needs both a multiplicity choice and an additivity-safe selector.

PER cannot override NOT ADDITIVE, and UNSAFE additivity cannot replace PER.

Choose the intended contribution

Next

• Metric additivity — validate which rollups preserve metric meaning
• Composability and purity — keep definition grain and population stable when features are reused
• Grouping & Unnesting — create coarser and finer result grains
• Window functions — broadcast aggregates without collapsing rows
• Entities and keys — identify the key that owns a value