Skip to main content

Aggregate Functions (Locked)

Aggregations at query time. R-AGG-01 requires every row-level field inside an aggregate context to be wrapped. R-AGG-02 prefers MIN/MAX over ATTR.

Basic Aggregates​

FunctionSignatureReturnNotes
SUMSUM(expression)NumberSums non-NULL values.
AVGAVG(expression)NumberIgnores NULL.
MEDIANMEDIAN(expression)NumberMiddle value.
COUNTCOUNT(expression)IntegerCounts non-NULL values.
COUNTDCOUNTD(expression)IntegerDistinct non-NULL count.
MINMIN(expression)AnyAggregate MIN.
MAXMAX(expression)AnyAggregate MAX.
ATTRATTR(expression)AnySame value across group, else NULL. See R-AGG-02.

Statistical Aggregates​

FunctionSignatureReturnNotes
STDEVSTDEV(expression)NumberSample standard deviation.
STDEVPSTDEVP(expression)NumberPopulation standard deviation.
VARVAR(expression)NumberSample variance.
VARPVARP(expression)NumberPopulation variance.
PERCENTILEPERCENTILE(expression, decimal)NumberNth percentile (0.0 to 1.0).
CORRCORR(x, y)NumberPearson correlation.
COVARCOVAR(x, y)NumberSample covariance.
COVARPCOVARP(x, y)NumberPopulation covariance.

Booleans in Aggregate Context​

Aggregating a Boolean: SUM converts TRUE to 1, FALSE to 0 (implicit numeric cast). Common pattern: SUM(IF [Sales] > 500 THEN 1 ELSE 0 END) counts orders above $500.

R-AGG-01 Wrapping Discipline​

Every row-level field inside an aggregate context needs an aggregation wrapper:

Wrong: IF SUM([Sales]) > 100 THEN [Sales] END Right: IF SUM([Sales]) > 100 THEN SUM([Sales]) END

Wrong: SUM([Sales]) / [Rows] where [Rows] is row-level Right: SUM([Sales]) / SUM([Rows])

R-AGG-02 ATTR-vs-MIN/MAX Discipline​

ATTR returns the value only if it's the same across all rows in the group; else NULL. This creates two documented failure modes:

  • NOT ATTR([BoolField]) fails when values are mixed (ATTR returns NULL, NOT NULL = NULL, not TRUE).
  • ISNULL(ATTR([DimField])) is TRUE both when the field is genuinely NULL and when values are mixed.

Prefer MIN or MAX when a stable-scalar assumption holds. Both return a single value from the group regardless of uniformity, avoiding the NULL trap.

Common Anti-Patterns​

  • Bare row-level fields in aggregate contexts (R-AGG-01 violation).
  • ATTR-based Booleans (R-AGG-02 violation).
  • SUM on a nullable field without ZN wrapping when zero is the intended fallback.
  • COUNTD on a very high-cardinality field (perf concern, R-PERF-01).

Version Notes​

All aggregate functions available Desktop 2020.1+.