Aggregate functions
Aggregate functions summarize many rows into one value, such as total revenue. Use them to turn row-level data into metrics in a formula. Add by to break the metric down by one or more properties. Before you start, review the NetScript language reference for formula syntax.
Aggregate syntax
Most aggregate functions accept two forms, so write the one that reads more clearly in your formula. The pipe form passes the input to the function. The function form takes the input as its first argument. The following formulas are equivalent:
INPUT | sum
sum(INPUT)Functions that take two inputs, such as percentile and corr, accept only the function form.
Without by, the result is a single value. Add by to group the result by one or more properties. The by operation works like a GROUP BY clause in Structured Query Language (SQL). Analytics joins the related datasets for you, based on the relationships in your data model. The following formula calculates the total amount for each ship mode:
Orders.Amount | sum by (Orders.ShipMode)To aggregate an aggregated result, add another aggregate function. The following formula calculates the total amount for each ship mode, then averages those totals:
Orders.Amount | sum by (Orders.ShipMode) | avgFunction names are not case-sensitive, so SUM and sum are equivalent. Aggregate functions skip null input values.
sum(INPUT)
Use the sum function to add up numeric values, such as revenue or quantity, across rows. When the input is a time interval, the result is also a time interval.
Function
INPUT | sumData type – number or time interval
Example
Orders.Amount | sumResult
If Orders.Amount is 10, 20, 30: result is 60avg(INPUT)
Use the avg function to calculate the average of numeric values, such as the average order amount. Analytics converts the input to a floating-point number before averaging, so the result keeps its fractional part. When the input is a time interval, the result is also a time interval.
Function
INPUT | avgData type – number or time interval
Example
Orders.Quantity | avgResult
If Orders.Quantity is 1, 2: result is 1.5min(INPUT)
Use the min function to find the smallest value, such as the earliest order date or the lowest price. The min function accepts a number, string, timestamp, time interval, date, or boolean.
The min function does not accept values of an unknown data type. Values of an unknown data type include JavaScript Object Notation (JSON) lookups and data warehouse functions. Convert these values with a type conversion function, such as to_float, before you aggregate them.
Function
INPUT | minData type – input type
Example
Orders.OrderDate | minResult
If Orders.OrderDate is 2026-01-05, 2026-03-12: result is 2026-01-05max(INPUT)
Use the max function to find the largest value, such as the most recent event time or the highest price. The max function accepts the same data types as the min function.
Function
INPUT | maxData type – input type
Example
Orders.Amount | maxResult
If Orders.Amount is 10, 20, 30: result is 30count(INPUT)
Use the count function to count rows or non-null values, such as the number of events or orders. Apply it to a dataset to count rows. Apply it to a property to count the rows where that property is not null.
Function
INPUT | countData type – number
Example
Orders | count
Orders | count by (Orders.ShipMode)Result
The number of orders
The number of orders for each ship modecount_distinct(INPUT)
Use the count_distinct function to count unique values, such as the number of unique users who triggered an event. The aliases countdistinct and uniques work the same way.
Function
INPUT | count_distinctData type – number
Example
Orders.CustomerID | count_distinctResult
If Orders.CustomerID is A, B, A: result is 2median(INPUT)
Use the median function to find the middle value of the input. Outliers affect the median less than the average. The median function accepts the same data types as the min function.
The median function is not available when your data warehouse is BigQuery.
Function
INPUT | medianData type – input type
Example
Orders.Amount | medianResult
If Orders.Amount is 10, 20, 90: result is 20stddev(INPUT)
Use the stddev function to measure how widely numeric values spread around their average. The function returns the population standard deviation. The aliases stddev_pop and stddevpop work the same way. When the input is a time interval, the result is also a time interval.
Function
INPUT | stddevData type – number or time interval
Example
Orders.Amount | stddevResult
If Orders.Amount is 10, 20, 30: result is 8.16percentile(INPUT, FRACTION)
Use the percentile function to find a cutoff value, such as the 95th percentile of order amounts. The input must be a number. The result is the value that the given fraction of input values does not exceed. Enter the fraction as a literal number, such as 0.95 for the 95th percentile. Your data warehouse requires a fraction between 0 and 1.
The percentile function interpolates between input values. The percentile_cont alias works the same way. The following variants are also available:
- percentile_disc – Returns an actual input value instead of an interpolated one. Redshift does not support this variant.
- approx_percentile – Returns an approximate value and runs faster on large datasets.
Function
percentile(INPUT, FRACTION)Data type – number
Example
percentile(Orders.Amount, 0.5)Result
If Orders.Amount is 10, 20, 30, 40: result is 25corr(INPUT_1, INPUT_2)
Use the corr function to measure how closely two numeric values move together, such as order amount and quantity. The result ranges from -1 to 1. A value near 1 means both values rise together. A value near -1 means one value falls as the other rises.
Function
corr(INPUT_1, INPUT_2)Data type – number
Example
corr(Orders.Amount, Orders.Quantity)Result
The correlation between order amount and quantity