--- title: Aggregate functions slug: analytics/docs/netscript-aggregate-functions createdAt: 2026-10-28T12:00:00.000Z --- 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](docId:8NvhL6PRcYM9S-k6jD1N1) 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: ```auto 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: ```auto 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: ```auto Orders.Amount | sum by (Orders.ShipMode) | avg ``` Function 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** ```auto INPUT | sum ``` **Data type** – number or time interval **Example** ```auto Orders.Amount | sum ``` **Result** ```auto If Orders.Amount is 10, 20, 30: result is 60 ``` ## `avg(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** ```auto INPUT | avg ``` **Data type** – number or time interval **Example** ```auto Orders.Quantity | avg ``` **Result** ```auto If Orders.Quantity is 1, 2: result is 1.5 ``` ## `min(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](docId:8NvhL6PRcYM9S-k6jD1N1). Convert these values with a type conversion function, such as `to_float`, before you aggregate them. **Function** ```auto INPUT | min ``` **Data type** – input type **Example** ```auto Orders.OrderDate | min ``` **Result** ```auto If Orders.OrderDate is 2026-01-05, 2026-03-12: result is 2026-01-05 ``` ## `max(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** ```auto INPUT | max ``` **Data type** – input type **Example** ```auto Orders.Amount | max ``` **Result** ```auto If Orders.Amount is 10, 20, 30: result is 30 ``` ## `count(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** ```auto INPUT | count ``` **Data type** – number **Example** ```auto Orders | count Orders | count by (Orders.ShipMode) ``` **Result** ```auto The number of orders The number of orders for each ship mode ``` ## `count_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** ```auto INPUT | count_distinct ``` **Data type** – number **Example** ```auto Orders.CustomerID | count_distinct ``` **Result** ```auto If Orders.CustomerID is A, B, A: result is 2 ``` ## `median(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** ```auto INPUT | median ``` **Data type** – input type **Example** ```auto Orders.Amount | median ``` **Result** ```auto If Orders.Amount is 10, 20, 90: result is 20 ``` ## `stddev(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** ```auto INPUT | stddev ``` **Data type** – number or time interval **Example** ```auto Orders.Amount | stddev ``` **Result** ```auto If Orders.Amount is 10, 20, 30: result is 8.16 ``` ## `percentile(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** ```auto percentile(INPUT, FRACTION) ``` **Data type** – number **Example** ```auto percentile(Orders.Amount, 0.5) ``` **Result** ```auto If Orders.Amount is 10, 20, 30, 40: result is 25 ``` ## `corr(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** ```auto corr(INPUT_1, INPUT_2) ``` **Data type** – number **Example** ```auto corr(Orders.Amount, Orders.Quantity) ``` **Result** ```auto The correlation between order amount and quantity ```