Aggregation Annotations

Aggregations compute values like sums, averages, and counts across groups of rows. In Atscript, you declare aggregations directly on view fields using @db.agg.* annotations — the database handles the computation.

Available Functions

@db.agg.sum

Computes the SUM of a numeric source column:

@db.agg.sum "amount"
totalAmount: number

The field argument is the source column name from the entry table. The annotated field must be number or decimal.

@db.agg.avg

Computes the AVG (average) of a numeric source column:

@db.agg.avg "amount"
averageAmount: number

Like sum, the field must be number or decimal.

@db.agg.count

Counts rows. Without an argument, it produces COUNT(*) — counting all rows. With a field name, it produces COUNT(field) — counting non-null values only:

@db.agg.count
totalOrders: number        // COUNT(*)

@db.agg.count "assigneeId"
assignedOrders: number     // COUNT(assigneeId) — excludes nulls

The annotated field must be number.

TIP

COUNT(*) counts all rows in each group, including those with null values. COUNT(field) only counts rows where the specified field is not null.

@db.agg.min

Minimum value of a source column:

@db.agg.min "amount"
smallestOrder: number

Accepts any comparable type — numbers, strings, dates.

@db.agg.max

Maximum value of a source column:

@db.agg.max "createdAt"
latestOrder: number.timestamp

Accepts any comparable type. Annotate the result field with the source column's primitive (here number.timestamp) so the view's TypeScript output preserves type fidelity.

The GROUP BY Pattern

When a view contains aggregation annotations, non-aggregated fields automatically become GROUP BY columns. This is how the database knows how to group the data before computing aggregates.

@db.view 'category_stats'
@db.view.for Order
export interface CategoryStats {
    category: Order.category      // plain field → GROUP BY

    @db.agg.sum "amount"
    totalRevenue: number          // aggregated

    @db.agg.count
    orderCount: number            // aggregated

    @db.agg.avg "amount"
    avgOrderValue: number         // aggregated
}

This produces SQL equivalent to:

SELECT category, SUM(amount) AS totalRevenue, COUNT(*) AS orderCount,
       AVG(amount) AS avgOrderValue
FROM orders
GROUP BY category

Multiple plain fields create multi-column grouping:

category: Order.category     // GROUP BY column 1
region: Order.region         // GROUP BY column 2

@db.agg.sum "amount"
totalRevenue: number         // aggregated per (category, region)

When all fields are aggregated (no plain fields), there is no GROUP BY — the aggregation runs across the entire table, producing a single result row.

Type Constraints

Annotation	Allowed field types	Validates at
@db.agg.sum	number, decimal	Compile time
@db.agg.avg	number, decimal	Compile time
@db.agg.count	number	Compile time
@db.agg.min	Any comparable	—
@db.agg.max	Any comparable	—

Atscript validates type compatibility at build time — annotating a string field with @db.agg.sum produces a compile error.

Runtime Aggregation: Quantity Dimensions

The annotations above define view-time aggregations: shape baked into the schema. For ad-hoc aggregations via table.aggregate() at runtime, see Queries & Filters § Runtime Aggregation.

One constraint worth knowing here, because it surfaces the same way against view aggregates: when the source column carries @db.amount.currency.ref or @db.unit.ref (see Annotations § Quantity Tagging), the runtime rejects ad-hoc aggregations that don't include the referenced dimension in $groupBy. Summing rows that mix currencies — or kg with lb — is meaningless, and the guard catches it before it reaches the database.

// Schema: amount carries @db.amount.currency.ref 'currency'
await orders.aggregate({
  filter: {},
  controls: {
    $groupBy: ["status"], // ← missing 'currency'
    $select: ["status", { $fn: "sum", $field: "amount", $as: "total" }],
  },
});
// → DbError("INVALID_QUERY"): Aggregate "sum(amount)" requires "currency" in $groupBy

Add the ref field to $groupBy (["status", "currency"]) and the query proceeds. Literal forms (@db.amount.currency 'EUR', @db.unit 'qps') impose no constraint — the dimension is fixed schema-wide. COUNT(*) is exempt either way.

Annotation Reference

Annotation	Argument	Required?	SQL Equivalent
@db.agg.sum "field"	Source column name	Yes	SUM(field)
@db.agg.avg "field"	Source column name	Yes	AVG(field)
@db.agg.count	None	—	COUNT(*)
@db.agg.count "field"	Source column name	Optional	COUNT(field)
@db.agg.min "field"	Source column name	Yes	MIN(field)
@db.agg.max "field"	Source column name	Yes	MAX(field)

Next Steps

• Aggregation Views — combining views with aggregation annotations
• Defining Views — view structure, joins, and filters
• Querying Views — reading aggregation results at runtime