> For the complete documentation index, see [llms.txt](https://docs.e6data.com/ingestion-engine/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.e6data.com/ingestion-engine/sql/aggregate-and-window-functions/aggregates.md). # Aggregates *The e6 Ingestion Engine aggregate functions are based on* [*Apache DataFusion*](https://arrow.apache.org/datafusion/) *and these docs are derived from the DataFusion function reference.* ## Quick reference | Function | Description | Example | | ----------------------- | -------------------------- | -------------------------------- | | `avg(expr)` | Average value | `avg(price)` | | `count(expr)` | Count of values | `count(*)` | | `count(DISTINCT expr)` | Count distinct values | `count(DISTINCT user_id)` | | `sum(expr)` | Sum of values | `sum(amount)` | | `min(expr)` | Minimum value | `min(price)` | | `max(expr)` | Maximum value | `max(price)` | | `median(expr)` | Median value | `median(price)` | | `array_agg(expr)` | Collect into array | `array_agg(name)` | | `first_value(expr)` | First value in group | `first_value(price ORDER BY ts)` | | `last_value(expr)` | Last value in group | `last_value(price ORDER BY ts)` | | `stddev(expr)` | Standard deviation | `stddev(price)` | | `var(expr)` | Variance | `var(price)` | | `approx_distinct(expr)` | Approximate count distinct | `approx_distinct(user_id)` | ## Worked example Given this `sales` data: | product | category | amount | | -------- | ----------- | ------ | | Widget A | Electronics | 29.99 | | Widget B | Electronics | 49.99 | | Gadget C | Home | 15.00 | | Widget D | Electronics | 35.00 | | Gadget E | Home | 22.50 | ```sql SELECT category, count(*) as num_products, sum(amount) as total, avg(amount) as avg_price, min(amount) as cheapest, max(amount) as priciest FROM sales GROUP BY category, TUMBLE(interval '1 minute') ``` Output: | category | num\_products | total | avg\_price | cheapest | priciest | | ----------- | ------------- | ------ | ---------- | -------- | -------- | | Electronics | 3 | 114.98 | 38.33 | 29.99 | 49.99 | | Home | 2 | 37.50 | 18.75 | 15.00 | 22.50 | ## General ### `avg` Returns the average of numeric values in the specified column. ``` avg(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. **Aliases** * `mean` ### `bit_and` Computes the bitwise AND of all non-null input values. ``` bit_and(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `bit_or` Computes the bitwise OR of all non-null input values. ``` bit_or(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `bit_xor` Computes the bitwise exclusive OR of all non-null input values. ``` bit_xor(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `bool_and` Returns true if all non-null input values are true, otherwise false. ``` bool_and(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `bool_or` Returns true if any non-null input value is true, otherwise false. ``` bool_or(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `count` Returns the number of rows in the specified column. Count includes *null* values in the total count. To exclude *null* values from the total count, include ` IS NOT NULL` in the `WHERE` clause. ``` count(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `max` Returns the maximum value in the specified column. ``` max(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `mean` *Alias of* [*avg*](#avg)*.* ### `median` Returns the median value in the specified column. ``` median(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `min` Returns the minimum value in the specified column. ``` min(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `sum` Returns the sum of all values in the specified column. ``` sum(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `array_agg` Returns an array created from the expression elements. If ordering requirement is given, elements are inserted in the order of required ordering. ``` array_agg(expression [ORDER BY expression]) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `first_value` Returns the first element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group. ``` first_value(expression [ORDER BY expression]) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `last_value` Returns the last element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group. ``` last_value(expression [ORDER BY expression]) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ## Statistical ### `corr` Returns the coefficient of correlation between two numeric values. ``` corr(expression1, expression2) ``` **Arguments** * **expression1**: First expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression2**: Second expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `covar` Returns the covariance of a set of number pairs. ``` covar(expression1, expression2) ``` **Arguments** * **expression1**: First expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression2**: Second expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `covar_pop` Returns the population covariance of a set of number pairs. ``` covar_pop(expression1, expression2) ``` **Arguments** * **expression1**: First expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression2**: Second expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `covar_samp` Returns the sample covariance of a set of number pairs. ``` covar_samp(expression1, expression2) ``` **Arguments** * **expression1**: First expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression2**: Second expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `stddev` Returns the standard deviation of a set of numbers. ``` stddev(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `stddev_pop` Returns the population standard deviation of a set of numbers. ``` stddev_pop(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `stddev_samp` Returns the sample standard deviation of a set of numbers. ``` stddev_samp(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `var` Returns the statistical variance of a set of numbers. ``` var(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `var_pop` Returns the statistical population variance of a set of numbers. ``` var_pop(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `var_samp` Returns the statistical sample variance of a set of numbers. ``` var_samp(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `regr_slope` Returns the slope of the linear regression line for non-null pairs in aggregate columns. Given input column Y and X: regr\_slope(Y, X) returns the slope (k in Y = k\*X + b) using minimal RSS fitting. ``` regr_slope(expression1, expression2) ``` **Arguments** * **expression\_y**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression\_x**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `regr_avgx` Computes the average of the independent variable (input) `expression_x` for the non-null paired data points. ``` regr_avgx(expression_y, expression_x) ``` **Arguments** * **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators. ### `regr_avgy` Computes the average of the dependent variable (output) `expression_y` for the non-null paired data points. ``` regr_avgy(expression_y, expression_x) ``` **Arguments** * **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators. ### `regr_count` Counts the number of non-null paired data points. ``` regr_count(expression_y, expression_x) ``` **Arguments** * **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators. ### `regr_intercept` Computes the y-intercept of the linear regression line. For the equation (y = kx + b), this function returns `b`. ``` regr_intercept(expression_y, expression_x) ``` **Arguments** * **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators. ### `regr_r2` Computes the square of the correlation coefficient between the independent and dependent variables. ``` regr_r2(expression_y, expression_x) ``` **Arguments** * **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators. ### `regr_sxx` Computes the sum of squares of the independent variable. ``` regr_sxx(expression_y, expression_x) ``` **Arguments** * **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators. ### `regr_syy` Computes the sum of squares of the dependent variable. ``` regr_syy(expression_y, expression_x) ``` **Arguments** * **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators. ### `regr_sxy` Computes the sum of products of paired data points. ``` regr_sxy(expression_y, expression_x) ``` **Arguments** * **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators. ## Approximate * [approx\_distinct](#approx_distinct) * [approx\_median](#approx_median) * [approx\_percentile\_cont](#approx_percentile_cont) * [approx\_percentile\_cont\_with\_weight](#approx_percentile_cont_with_weight) ### `approx_distinct` Returns the approximate number of distinct input values calculated using the HyperLogLog algorithm. ``` approx_distinct(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `approx_median` Returns the approximate median (50th percentile) of input values. It is an alias of `approx_percentile_cont(x, 0.5)`. ``` approx_median(expression) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. ### `approx_percentile_cont` Returns the approximate percentile of input values using the t-digest algorithm. ``` approx_percentile_cont(expression, percentile, centroids) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **percentile**: Percentile to compute. Must be a float value between 0 and 1 (inclusive). * **centroids**: Number of centroids to use in the t-digest algorithm. *Default is 100*. If there are this number or fewer unique values, you can expect an exact result. A higher number of centroids results in a more accurate approximation, but requires more memory to compute. ### `approx_percentile_cont_with_weight` Returns the weighted approximate percentile of input values using the t-digest algorithm. ``` approx_percentile_cont_with_weight(expression, weight, percentile) ``` **Arguments** * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **weight**: Expression to use as weight. Can be a constant, column, or function, and any combination of arithmetic operators. * **percentile**: Percentile to compute. Must be a float value between 0 and 1 (inclusive). --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.e6data.com/ingestion-engine/sql/aggregate-and-window-functions/aggregates.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.