Aggregate Functions
Introduction
SQL Server currently supports three operators that can compute aggregations: Hash Match, Stream Aggregate, and Window Aggregate. These operators all use the same basic principle of maintaining internal counters as rows are processed, so that the final value of those internal counters is the expected value.
Supported aggregate functions
The list of aggregate functions that is supported by the aggregation operators is not a direct match of the list of aggregate functions supported by T-SQL. Some aggregate functions that are allowed in a query are not computed directly in an execution plan, but through a workaround. For instance, if a query uses the AVG function, the execution plan computes two values, a COUNT and a SUM, and then uses a Compute Scalar operator to compute the requested average by dividing these two values (often with some special logic to handle empty sets).
On the other hand, there are also aggregation functions that are used in aggregation operators but not allowed in T-SQL, such as ANY.
The table below lists all currently known aggregate functions, with a description, and details of the initial value assigned to the internal counters and how those counters are updated as rows are processed. In some cases there are multiple ways to achieve the same result; since Microsoft has not released any official documentation I have listed the most likely method in those cases.
| Function name | Description | Initialize counters | Update counters |
|---|---|---|---|
| ANY (expression) | Returns one of the values seen for expression in the group. Though technically any of the values for expression from the input rows could be considered valid, the actual implementation of this function always returns the value from the first row it receives. | Set to expression. | Leave unchanged. |
| APPROX_COUNT_DISTINCT_ACCUM (expression) | Returns a set of values, split based on a hash of expression, with for each set the highest position of the leftmost “1” bit in the hash of expression in that set. (See: hyperloglog). Note that the returned result is a single value, probably encoded by combining the values for each set into a single value. Introduced in SQL Server 2019. | Set to null for each set. | Compute the hash of expression; use the right-most bits to determine the set; compute the position of the leftmost “1” bit in the hash; update the counter if this is higher than the previous value. |
| APPROX_PERCENTILE_ACCUM (expression) | Returns a data sketch that represents the input data, or a random sampling of it when the total input data size is too large. The data sketch is a sorted list of each distinct non-null input value, with a “weight” to represent the cardinality of that value. (See: KLL sketch). Note that the sketch is returned as a single value, probably encoded by combining all values and weights into a single value. Introduced in SQL Server 2022. | Set to an empty sketch. | If expression is not null, then increase its weight if it’s already in the sketch, or add it with weight 1 otherwise. If this pushes the sketch past its maximum size, add the weights of each pair of consecutive entries and then remove all entries in either odd- or even-numbered positions (chosen at random). |
| CHECKSUM_AGG (expression) | Returns a checksum value computed from all non-null values of expression within the group. | Set to null. | Currently unknown. |
| Count (*) | Returns the total number of rows in the group, regardless of input values, as a bigint. | Set to 0. | Increment by 1. |
| COUNT (expression) | Returns the total number of rows in the group where expression is not null, as an int. | Set to 0. | If expression is not null, then increment by 1. |
| COUNT_BIG (expression) | Returns the total number of rows in the group where expression is not null, as a bigint. | Set to 0. | If expression is not null, then increment by 1. |
| FIRST_VALUE_IS_NULL (expression) | Returns true if the first value of expression within the group is null. | Set to null. | If counter is null then set to true if expression is null or set to false if expression is not null. |
| FIRST_VALUE_IGNORE_NULLS (expression) | Returns the first non-null value of expression within the group. | Set to null. | If counter is null then set to expression. |
| JSON_ARRAYAGG (expression, null_handling, return_type) | Returns a JSON array that consists of all (scalar) expressions. The array is returned as data type json when return_type is -201326592, or as nvarchar(max) if return_type is 231. | Set to null. | If expression is null and null_handling is 1 then leave unchanged; else if counter is null then set to a JSON array with one element with value expression; else add an value expression as the last element in the JSON array in counter. |
| JSON_OBJECTAGG (key, value, null_handling, return_type) | Returns a JSON object that consists of all key/value pairs. The object is returned as data type json when return_type is -201326592, or as nvarchar(max) if return_type is -402653440. | Set to null. | If value is null and null_handling is 1 then leave unchanged; else if counter is null then set to a JSON object with one element key/value; else add an element with key/value at the end of the JSON object in counter. |
| LAST_VALUE_IGNORE_NULLS (expression) | Returns the last non-null value of expression within the group. Introduced in SQL Server 2022. | Set to null. | If expression is not null, then set to expression. |
| LAST_VALUE (expression) | Returns the last value of expression within the group (regardless of whether or not it is null). | Set to null. | Set to expression. |
| MAX (expression) | Returns the maximum non-null value of expression in the group. | Set to null. | If counter is null or expression > counter, then set to expression. |
| MIN (expression) | Returns the minimum non-null value of expression in the group. | Set to null. | If counter is null or expression < counter, then set to expression. |
| PRODUCT (expression) | Returns the product of all non-null values of expression in the group. | Set to null. | If expression is null then leave unchanged; else if counter is null then set to expression; else set to counter * expression. |
| STATMAN (expression) | Used in execution plans for UPDATE STATISTICS. expression is the column for which statistics are updated. It is currently unknown what data type is returned by STATMAN, nor what data is represented in the returned value. | Currently unknown. | Currently unknown. |
| STATMANMERGE (expression) | Used in parallel execution plans for UPDATE STATISTICS. expression is the output of the STATMAN aggregation function on one of the threads. STATMANMERGE combines the results of all threads in a serial section of the execution plan. It is currently unknown what data type is returned by STATMANMERGE, nor what data is represented in the returned value. | Currently unknown. | Currently unknown. |
| STRING_AGG (expression, separator) | Returns a string consisting of all non-null values of expression within the group, separated by separator. Introduced in SQL Server 2017. | Set to null. | If counter is null then set to expression; else set to counter + separator + expression. |
| SUM (expression) | Returns the sum of all non-null values of expression in the group. | Set to 0. | If expression is not null then set to counter + expression. |
| Any CLR user defined aggregate function | Returns values as determined by the CLR code. | Executes the Init() method of the CLR user defined aggregate. | Executes the Accumulate() method of the CLR user defined aggregate, passing it the value of expression. |
Missing any function?
There is no official documentation (that I know of) of the supported aggregate functions. The list above has been compiled based on what I have observed in execution plans that I have seen. It is very likely that this list is not complete yet.
You can help me complete this list! If you encounter an execution plan where Compute Scalar uses a keyword not listed here, please save it as a .sqlplan file and send it to me.
Thanks!
Change log
(Does not include minor changes, such as adding, removing, or changing hyperlinks, correcting typos, and rephrasing for clarity).
December 3, 2020: Added.
April 1, 2022: Corrected an error in the description of the ANY function; put in a disclaimer that some functions could be implemented in a different way to get the same result.
November 10, 2023: Added the LAST_VALUE_IGNORE_NULLS function; small clarification for ANY.
December 12, 2023: Added the APPROX_PERCENTILE_ACCUM function; added in which version an aggregate function was introduced (where known).
January 16, 2024: Added that LAST_VALUE_IGNORE_NULLS was introduced in SQL Server 2022.
April 14, 2024: Added the COUNT_BIG, STATMAN and STATMANMERGE aggregate functions; added data type of the returned value for Count (*), COUNT, and COUNT_BIG.
January 7, 2026: Added JSON_ARRAYAGG, JSON_OBJECTAGG, and PRODUCT; corrected the description for APPROX_PERCENTILE_ACCUM.
