Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric
Use analytical and aggregate T-SQL functions to summarize data, compare individual rows with group statistics, calculate percentiles, rank results, and combine values into strings or JSON. You can run these calculations directly in a warehouse or SQL analytics endpoint.
Many functions have two forms: an aggregate form that summarizes rows and a window form that preserves detail rows. Other functions support only one form. This article groups functions by purpose and explains how to choose the form and window options for your calculation.
The examples use VALUES to define small input tables directly in the query, so you don't
need to create tables first. In AS s(Region, Amount), s names the input table, and
Region and Amount name its columns. NULL represents a missing or unknown value.
Aggregate functions
Use aggregate functions to calculate summary characteristics of a data set, such as its total,
minimum, and maximum values. Each function returns one value for each group when you use
GROUP BY, or one value for the entire input when you omit it. For example, this query
returns the total, smallest amount, and largest amount for each region:
SELECT Region, SUM(Amount) AS RegionTotal,
MIN(Amount) AS MinAmount,
MAX(Amount) AS MaxAmount
FROM (VALUES ('North', 10), ('North', 20),
('South', 30), ('South', 40)) AS s(Region, Amount)
GROUP BY Region
ORDER BY Region;
The result has one row per region:
| Region | RegionTotal | MinAmount | MaxAmount |
|---|---|---|---|
| North | 30 | 10 | 20 |
| South | 70 | 30 | 40 |
GROUP BY combines the sales into one summary row per region. SUM, MIN, and MAX
describe that region's sales, not the entire input.
Use aggregate functions in the SELECT list to return summaries and in HAVING
to filter those summaries. Grouped aggregation doesn't use an OVER clause.
Filter aggregate results with HAVING
Use HAVING
to filter groups based on an aggregate calculation. WHERE filters individual input rows
before aggregation, so it can't filter on that query's aggregate result.
This query keeps only regions whose total is greater than 50:
SELECT Region, SUM(Amount) AS RegionTotal
FROM (VALUES ('North', 10), ('North', 20),
('South', 30), ('South', 40)) AS s(Region, Amount)
GROUP BY Region
HAVING SUM(Amount) > 50
ORDER BY Region;
Only South qualifies:
| Region | RegionTotal |
|---|---|
| South | 70 |
HAVING tests the totals after grouping: South's 70 passes the filter, while North's
30 doesn't.
Window functions
Use window functions to calculate results for individual rows without collapsing them into
groups. The OVER clause defines partitioning, ordering, and, where supported, a frame.
What is a window?
A window relates a set of rows to the current row through an
OVER clause.
Unlike grouped aggregation, a window function doesn't collapse those rows into a summary row.
It adds a calculation to each row of its input result.
For example, SUM(Amount) OVER () adds the total for the entire input to every sale.
SUM(Amount) OVER (PARTITION BY Region) adds the total for that sale's region instead.
The partition determines which rows are calculated together. For functions that support it, a window frame narrows that partition to the rows used for the current row's calculation, such as the current row and two preceding rows.
PARTITION BY
Use PARTITION BY inside OVER to calculate results independently for each set of matching
partition values. Unlike GROUP BY, it preserves individual input rows. If you omit
PARTITION BY, all input rows belong to one partition.
This query keeps all four sales and adds the overall total and the total for each sale's region:
SELECT SaleId, Region, Amount,
SUM(Amount) OVER () AS OverallTotal,
SUM(Amount) OVER (PARTITION BY Region) AS RegionTotal
FROM (VALUES (1, 'North', 10), (2, 'North', 20),
(3, 'South', 30), (4, 'South', 40)) AS s(SaleId, Region, Amount)
ORDER BY SaleId;
The result keeps one row per sale:
| SaleId | Region | Amount | OverallTotal | RegionTotal |
|---|---|---|---|---|
| 1 | North | 10 | 100 | 30 |
| 2 | North | 20 | 100 | 30 |
| 3 | South | 30 | 100 | 70 |
| 4 | South | 40 | 100 | 70 |
SUM(Amount) OVER () uses all four sales, so OverallTotal is 100 on every row.
SUM(Amount) OVER (PARTITION BY Region) uses only rows with the same region, so RegionTotal
is 30 for North and 70 for South.
ORDER BY
The ORDER BY clause inside OVER defines the logical order for the calculation. For example, it
determines which row precedes the current row for LAG, how ROW_NUMBER numbers rows, and
how an ordered frame progresses.
For functions that accept frames, adding window ORDER BY also changes the default frame.
For example, SUM becomes a running calculation unless you specify a different frame.
See RANGE.
This ordering doesn't guarantee the order of the final result. Add an outer ORDER BY when
you need sorted output. It's also separate from WITHIN GROUP (ORDER BY ...) and ordering
inside an aggregate such as JSON_ARRAYAGG.
Window ordering is required for ranking functions and for FIRST_VALUE, LAST_VALUE,
LAG, and LEAD. It's optional for many aggregates, including ANY_VALUE, and isn't
supported for the median and percentile window functions described here.
When the order must identify a specific row, add a unique tie-breaker, such as
ORDER BY DayNumber, SaleId. Keep ties when they are meaningful to the calculation, such
as equal amounts receiving the same RANK.
ROWS
Use ROWS to define a frame by row positions in the window order. For example:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWincludes every preceding row and the current row, which is useful for running totals.ROWS BETWEEN 2 PRECEDING AND CURRENT ROWincludes up to three rows, which is useful for moving averages.ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGincludes the entire partition.
These are row counts, not time intervals. Two preceding rows aren't necessarily two
preceding days. Use a unique ordering when you need reproducible row-position boundaries.
Only functions that support frames accept ROWS.
RANGE
Use RANGE for a frame whose boundaries account for ordering values. Rows with equal
window ordering values are peers. In a frame that ends at CURRENT ROW, RANGE includes
all peers of the current row, whereas ROWS ends at the current row position.
This example uses the first three sales: two on day 1 and one on day 2. ROWS adds one
sale at a time, using SaleId to break ties. RANGE orders only by DayNumber, so it
includes both day-1 sales together.
SELECT SaleId, DayNumber, Amount,
SUM(Amount) OVER (ORDER BY DayNumber, SaleId
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningByRow,
SUM(Amount) OVER (ORDER BY DayNumber
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningThroughDay
FROM (VALUES (1, 1, 10), (2, 1, 20), (3, 2, 30)) AS s(SaleId, DayNumber, Amount)
ORDER BY DayNumber, SaleId;
| SaleId | DayNumber | Amount | RunningByRow | RunningThroughDay |
|---|---|---|---|---|
| 1 | 1 | 10 | 10 | 30 |
| 2 | 1 | 20 | 30 | 30 |
| 3 | 2 | 30 | 60 | 60 |
Both day-1 sales show RunningThroughDay = 30. RunningByRow adds one sale at a time.
RANGE supports CURRENT ROW and unbounded boundaries, but not numeric offsets such as
RANGE 5 PRECEDING. Both ROWS and RANGE require window ordering.
For functions that accept frames, specifying window ORDER BY without an explicit frame
defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This default matters for
LAST_VALUE: to get the last value in the entire partition, explicitly end the frame
with UNBOUNDED FOLLOWING, rather than relying on the default. If you omit window
ORDER BY where it's optional, the calculation covers the whole partition.
Frame boundaries for ROWS and RANGE
Use BETWEEN to choose a start boundary and an end boundary from the following table.
Here, n is a nonnegative integer literal, such as 2.
| Boundary | Meaning | ROWS | RANGE | Allowed position |
|---|---|---|---|---|
UNBOUNDED PRECEDING |
The first row of the partition. | Yes | Yes | Start only. |
n PRECEDING |
The row n positions before the current row. |
Yes | No | Start or end. |
CURRENT ROW |
The current row for ROWS; the first peer for a RANGE start or the last peer for a RANGE end. |
Yes | Yes | Start or end. |
n FOLLOWING |
The row n positions after the current row. |
Yes | No | Start or end. |
UNBOUNDED FOLLOWING |
The last row of the partition. | Yes | Yes | End only. |
Boundaries follow the window ORDER BY, even when it sorts descending. Both endpoints
are inclusive, and the end can't precede the start. Frames don't extend beyond their
partition, so fewer rows can be available near its ends.
For example:
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWINGincludes the current row and up to two following rows.ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWINGincludes up to five rows: the current row, two before it, and two after it.RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGincludes the current row's entire peer group and all following rows in the partition.
ROWS 2 PRECEDING is shorthand for ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
To include following rows, use the full BETWEEN ... AND ... form. Both frame types
require window ordering and a function that supports frames.
Reuse a window with WINDOW
Use the WINDOW clause
to name a window definition once and reuse it in multiple OVER clauses within the same
query. This approach avoids repeating the same partitioning, ordering, or frame definition.
This query defines RegionWindow once and uses it for both SUM and MAX:
SELECT Region, Amount,
SUM(Amount) OVER RegionWindow AS RegionTotal,
MAX(Amount) OVER RegionWindow AS RegionMax
FROM (VALUES ('North', 10), ('North', 20),
('South', 30), ('South', 40)) AS s(Region, Amount)
WINDOW RegionWindow AS (PARTITION BY Region)
ORDER BY Region, Amount;
| Region | Amount | RegionTotal | RegionMax |
|---|---|---|---|
| North | 10 | 30 | 20 |
| North | 20 | 30 | 20 |
| South | 30 | 70 | 40 |
| South | 40 | 70 | 40 |
Both functions reuse the region partition, so the total and maximum values repeat on every sale in that region. Each function still follows its own restrictions on window ordering and frames.
Filter window results with QUALIFY
Use QUALIFY
to filter on window function results after those functions are evaluated. Window functions
can't appear in WHERE or HAVING at the same query level. Use HAVING for aggregate
results and QUALIFY for window results.
For example, QUALIFY lets you return the highest-value sale in each region without
wrapping the window calculation in another query:
SELECT SaleId, Region, Amount
FROM (VALUES (1, 'North', 10), (2, 'North', 20),
(3, 'South', 30), (4, 'South', 40)) AS s(SaleId, Region, Amount)
QUALIFY ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Amount DESC, SaleId) = 1
ORDER BY SaleId;
The result keeps the largest sale in each region:
| SaleId | Region | Amount |
|---|---|---|
| 2 | North | 20 |
| 4 | South | 40 |
ROW_NUMBER assigns 1 to the largest sale in each region. QUALIFY keeps those sales,
rather than combining them into regional totals.
Function groups
Choose a function by the calculation you need, then select its aggregate or window form where supported. The groups below describe purpose rather than execution mode. For a comparison of forms and window options, see Compare aggregate and window support.
Basic aggregates
Use these functions for common summaries, such as sales totals, row counts, minimum and
maximum values, and averages. Each function supports aggregate and window forms, but the
available modifiers can differ. For example, COUNT(DISTINCT expression) is aggregate-only.
| Function | Purpose |
|---|---|
| COUNT | Counts rows or non-NULL expression values and returns int. |
| COUNT_BIG | Counts rows or non-NULL expression values and returns bigint. |
| SUM | Adds numeric values. |
| MIN | Returns the smallest value. |
| MAX | Returns the largest value. |
| AVG | Calculates the arithmetic mean. |
| PRODUCT | Multiplies numeric values together. |
Choose the return type deliberately. For example, AVG over an int expression returns
int. Cast the input, such as AVG(CAST(Amount AS decimal(18, 2))), when you need a
fractional average. Use COUNT_BIG when a count might exceed the int range.
Statistics and percentiles
Use these functions to describe a distribution, find a typical value, or measure variability. All support aggregate and window forms, but their window options differ.
A percentile describes a position in sorted data. The 50th percentile is the median, and the 95th percentile (P95) is near the high end. Variance and standard deviation measure how spread out the values are.
Use VAR and STDEV when the input is a sample of a larger population; use VARP and
STDEVP when it represents the whole population.
| Function | Purpose |
|---|---|
| MEDIAN | Calculates the exact continuous 50th percentile. |
| QUANTILE | Calculates an exact continuous quantile, such as the 95th percentile. |
| PERCENTILE_CONT | Calculates a continuous percentile using WITHIN GROUP ordering. |
| PERCENTILE_DISC | Calculates a discrete percentile that is a value from the input. |
| VAR | Calculates sample variance. |
| VARP | Calculates population variance. |
| STDEV | Calculates sample standard deviation. |
| STDEVP | Calculates population standard deviation. |
Exact median and quantiles
A continuous percentile interpolates between adjacent values when necessary, so the result
might not occur in the input. For example, the median of 10 and 20 is 15. A discrete
percentile selects an input value instead.
MEDIAN and QUANTILE have the following forms. Square brackets mark optional syntax,
and ...n indicates a list that can repeat:
MEDIAN ( numeric_expression )
MEDIAN ( numeric_expression ) OVER ( [ PARTITION BY partition_expression [ , ...n ] ] )
QUANTILE ( numeric_literal, numeric_expression )
QUANTILE ( numeric_literal, numeric_expression )
OVER ( [ PARTITION BY partition_expression [ , ...n ] ] )
The quantile argument is a numeric literal from 0 through 1, inclusive. Specify 0.5
for the median or 0.95 for the 95th percentile. Both functions return float(53), ignore
NULL values, and return NULL when no non-NULL input values qualify. They don't support
DISTINCT or window ordering and frames.
In Fabric, PERCENTILE_CONT and PERCENTILE_DISC also support both forms: use
WITHIN GROUP (ORDER BY expression) with or without OVER. This ordering determines the
percentile distribution; it isn't an ORDER BY inside OVER. Their window forms accept
optional partitioning, but not window ordering or frames.
For example, the aggregate and window forms of PERCENTILE_CONT are:
PERCENTILE_CONT ( numeric_literal )
WITHIN GROUP ( ORDER BY order_expression [ ASC | DESC ] )
PERCENTILE_CONT ( numeric_literal )
WITHIN GROUP ( ORDER BY order_expression [ ASC | DESC ] )
OVER ( [ PARTITION BY partition_expression [ , ...n ] ] )
PERCENTILE_DISC uses the same forms for discrete percentiles.
This example calculates exact median and percentile amounts for each region:
SELECT Region, MEDIAN(Amount) AS MedianAmount,
QUANTILE(0.95, Amount) AS P95Amount,
PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY Amount) AS DiscreteP95Amount
FROM (VALUES ('North', 10), ('North', 20), ('North', 30),
('South', 5), ('South', 15), ('South', 25)) AS s(Region, Amount)
GROUP BY Region
ORDER BY Region;
| Region | MedianAmount | P95Amount | DiscreteP95Amount |
|---|---|---|---|
| North | 20 | 29 | 30 |
| South | 15 | 24 | 25 |
The continuous percentile interpolates between amounts, giving 29 for North.
The discrete percentile selects an input amount, giving 30.
Approximate aggregates
Approximate aggregates use estimation algorithms to trade some precision for potentially lower memory use or calculation time on large inputs. Use them for exploratory analysis or dashboards when an estimate is sufficient. Choose exact functions when the calculation requires an exact result, and measure performance with your own data rather than assuming every approximate query is faster.
| Function | Purpose | Forms |
|---|---|---|
| APPROX_COUNT_DISTINCT | Estimates the number of distinct non-NULL values. | Aggregate only. |
| APPROX_MEDIAN | Estimates the continuous 50th percentile. | Aggregate and window. |
| APPROX_QUANTILE | Estimates a continuous quantile. | Aggregate and window. |
| APPROX_PERCENTILE_CONT | Estimates a continuous percentile using WITHIN GROUP ordering. |
Aggregate only. |
| APPROX_PERCENTILE_DISC | Estimates a discrete percentile from the input values. | Aggregate only. |
APPROX_COUNT_DISTINCT uses HyperLogLog, with a documented error rate of up to 2% within
97% probability. APPROX_PERCENTILE_CONT and APPROX_PERCENTILE_DISC use KLL sketches,
with a documented rank error of up to 1.33% within 99% confidence. Rank error measures a
position in the sorted distribution, not the percentage difference in the returned value.
These algorithm descriptions and numerical guarantees are function-specific. The
APPROX_MEDIAN and APPROX_QUANTILE references don't specify the same numerical
guarantees. Their results can differ from exact results and can vary across executions.
Approximate median and quantiles
Use these forms to estimate a median or quantile for a group or partition:
APPROX_MEDIAN ( numeric_expression )
APPROX_MEDIAN ( numeric_expression )
OVER ( [ PARTITION BY partition_expression [ , ...n ] ] )
APPROX_QUANTILE ( numeric_literal, numeric_expression )
APPROX_QUANTILE ( numeric_literal, numeric_expression )
OVER ( [ PARTITION BY partition_expression [ , ...n ] ] )
Like the exact counterparts, both functions return float(53) and ignore NULL values.
The quantile argument is a literal from 0 through 1, inclusive. They return NULL when
there are no non-NULL input values and don't support DISTINCT, window ordering, or frames.
For example, this query compares exact and approximate results:
SELECT MEDIAN(value) AS ExactMedian,
APPROX_MEDIAN(value) AS EstimatedMedian,
QUANTILE(0.95, value) AS ExactP95,
APPROX_QUANTILE(0.95, value) AS EstimatedP95
FROM GENERATE_SERIES(1, 1000, 1);
The exact results are fixed; the approximate columns can vary:
| ExactMedian | EstimatedMedian | ExactP95 | EstimatedP95 |
|---|---|---|---|
| 500.5 | Varies | 950.05 | Varies |
The approximate columns return numeric estimates. Varies indicates that their actual
values can change between executions.
Ranking and distribution
Use these functions to assign row positions, preserve ranking ties, divide rows into
buckets, or describe a row's position within a distribution. Each function requires OVER
with ORDER BY. Partitioning is optional, and window frames aren't supported.
| Function | Purpose |
|---|---|
| ROW_NUMBER | Assigns a unique sequential number within a partition. |
| RANK | Assigns equal ranks to ties and leaves gaps after ties. |
| DENSE_RANK | Assigns equal ranks to ties without gaps between ranks. |
| NTILE | Divides ordered rows into a specified number of buckets. |
| PERCENT_RANK | Calculates relative rank on a scale from zero through one. |
| CUME_DIST | Calculates the proportion of partition rows that precede or tie with the current row. |
For amounts 100, 100, and 80 ordered descending, RANK returns 1, 1, and 3;
DENSE_RANK returns 1, 1, and 2. ROW_NUMBER assigns different numbers to the
two tied rows, but without a tie-breaker, which tied row receives which number isn't
guaranteed.
Value selection and navigation
Use these functions to choose a representative value, find a value at an ordered window boundary, or compare the current row with a preceding or following row.
| Function | Purpose | Forms |
|---|---|---|
| ANY_VALUE | Selects an arbitrary non-NULL value when one is available. | Aggregate and window. |
| FIRST_VALUE | Returns the first value in the window frame, optionally skipping NULLs. | Window only. |
| LAST_VALUE | Returns the last value in the window frame, optionally skipping NULLs. | Window only. |
| LAG | Returns a value at a preceding row offset, which defaults to one. | Window only. |
| LEAD | Returns a value at a following row offset, which defaults to one. | Window only. |
Choose ANY_VALUE when any representative non-NULL value is acceptable. It doesn't promise
random sampling or a particular first or last value. Adding window ordering doesn't turn
it into FIRST_VALUE or LAST_VALUE.
FIRST_VALUE, LAST_VALUE, LAG, and LEAD require window ordering. By default, they respect NULL values (RESPECT NULLS). Add IGNORE NULLS between the function call and
OVER to skip NULL inputs when selecting a value.
FIRST_VALUE and LAST_VALUE support frames; LAG and LEAD use row offsets instead.
This query shows the preceding and final sale amounts. The explicit frame ensures that
LAST_VALUE reaches the end of the partition.
SELECT SaleId, Amount,
LAG(Amount) OVER (ORDER BY SaleId) AS PreviousAmount,
LAST_VALUE(Amount) OVER (ORDER BY SaleId
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastAmount
FROM (VALUES (1, 10), (2, 20), (3, 30)) AS s(SaleId, Amount)
ORDER BY SaleId;
| SaleId | Amount | PreviousAmount | LastAmount |
|---|---|---|---|
| 1 | 10 | NULL | 30 |
| 2 | 20 | 10 | 30 |
| 3 | 30 | 20 | 30 |
The previous amounts are NULL, 10, and 20; the final amount is 30 on every row.
Without the explicit frame, LastAmount would be 10, 20, and 30 instead of 30
on every row.
String and JSON aggregation
Use STRING_AGG to build delimited text, JSON_ARRAYAGG to build a JSON array, or
JSON_OBJECTAGG to build a JSON object from key-value pairs. These functions support
aggregate forms only; they don't accept OVER.
| Function | Purpose | Ordering and NULL behavior |
|---|---|---|
| STRING_AGG | Concatenates values with a separator. | Orders with WITHIN GROUP; skips NULL values and their separators. |
| JSON_ARRAYAGG | Combines values into a JSON array. | Accepts internal ORDER BY; defaults to ABSENT ON NULL, which omits NULL elements. |
| JSON_OBJECTAGG | Combines key-value pairs into a JSON object. | Defaults to NULL ON NULL, which preserves a NULL value as a JSON null. |
STRING_AGG derives its return type from the input expression. Cast the expression to
varchar(max) when the concatenation could exceed the size limit of a non-max result:
SELECT STRING_AGG(CAST(ProductName AS varchar(max)), ', ')
WITHIN GROUP (ORDER BY ProductName) AS ProductNames
FROM (VALUES ('Notebook'), ('Pen'), ('Pencil')) AS p(ProductName);
| ProductNames |
|---|
| Notebook, Pen, Pencil |
The names are alphabetized and joined with a comma and space.
JSON aggregation returns JSON text rather than a delimiter-separated list:
SELECT JSON_ARRAYAGG(ProductName ORDER BY ProductName) AS ProductNames,
JSON_OBJECTAGG(ProductCode:ProductName) AS ProductsByCode
FROM (VALUES ('P1', 'Pen'), ('P2', 'Pencil')) AS p(ProductCode, ProductName);
| ProductNames | ProductsByCode |
|---|---|
["Pen","Pencil"] |
{"P1":"Pen","P2":"Pencil"} |
The WITHIN GROUP (ORDER BY ...) clause in the STRING_AGG example and the internal
ORDER BY in the JSON_ARRAYAGG example control aggregation order, not a window
calculation. JSON object property order isn't guaranteed. Don't rely on an outer
query's ORDER BY to control concatenation or array element order.
Grouping helpers and checksums
Use grouping helpers to interpret subtotal rows, and aggregate checksums when a non-unique summary of values is sufficient.
| Function | Purpose | Forms |
|---|---|---|
| GROUPING | Identifies whether a grouping expression represents an aggregated subtotal. | Grouped queries only. |
| GROUPING_ID | Encodes the grouping status of multiple expressions as an integer bitmask. | Grouped queries only. |
| CHECKSUM_AGG | Calculates an aggregate checksum of integer expressions. | Aggregate and window. |
GROUPING and GROUPING_ID describe grouping levels rather than calculate a measure.
Use them with grouping sets, ROLLUP, or CUBE to distinguish a subtotal's NULL
placeholder from an actual NULL value in the data.
ROLLUP(Region) adds a grand-total row to the rows for individual regions.
SELECT Region, SUM(Amount) AS TotalAmount,
GROUPING(Region) AS IsSubtotal,
GROUPING_ID(Region) AS GroupingLevel
FROM (VALUES ('North', 10), ('South', 20), (NULL, 5)) AS s(Region, Amount)
GROUP BY ROLLUP(Region)
ORDER BY IsSubtotal, Region;
| Region | TotalAmount | IsSubtotal | GroupingLevel |
|---|---|---|---|
| NULL | 5 | 0 | 0 |
| North | 10 | 0 | 0 |
| South | 20 | 0 | 0 |
| NULL | 35 | 1 | 1 |
In this example, the actual NULL region has IsSubtotal = 0, while the grand total has
IsSubtotal = 1. With one grouping expression, GROUPING_ID returns the same value as
GROUPING; use multiple expressions to distinguish more grouping levels.
CHECKSUM_AGG can produce the same checksum for different inputs. Don't use it as proof
that two data sets are identical or as guaranteed change detection.
Usage considerations
Keep NULL handling, data types, and query evaluation order in mind:
COUNT(*)counts rows, including rows that contain NULLs.COUNT(expression)counts only non-NULL expression values. Most numeric aggregates ignore NULLs, but don't assume that every function has the same NULL behavior.- An ungrouped
COUNTorCOUNT_BIGover an empty input returns zero. Many other aggregates return NULL. A grouped query with no qualifying groups returns no rows. - Eliminating NULL values can produce the warning "Null value is eliminated by an aggregate or other SET operation." This warning doesn't mean the query failed.
- Choose numeric types that can hold the calculation. Sums and products can overflow, and integer averages can discard the fractional part.
- Use
WHEREto filter rows before grouping and window evaluation,HAVINGto filter grouped results, andQUALIFYto filter window results. Window functions operate on the input remaining after earlier query processing, not necessarily every row in the underlying table. The finalORDER BYsorts the remaining output.
Compare aggregate and window support
Unlike the purpose groups, rows in this table combine functions with the same capabilities. The following table compares the forms and window components for all functions in this article. A supported aggregate form doesn't imply that every argument or modifier is available in the window form. For example, COUNT(DISTINCT expression) doesn't support OVER.
Note
Functions that require OVER are ROW_NUMBER, RANK, DENSE_RANK, NTILE,
PERCENT_RANK, CUME_DIST, FIRST_VALUE, LAST_VALUE, LAG, and LEAD.
Functions without an OVER form are APPROX_COUNT_DISTINCT,
APPROX_PERCENTILE_CONT, APPROX_PERCENTILE_DISC, STRING_AGG, JSON_ARRAYAGG,
JSON_OBJECTAGG, GROUPING, and GROUPING_ID.
Supported and Not supported describe function forms. Optional, Required, and
Not supported describe components inside OVER. N/A means the function doesn't
have an OVER form. Optional partitioning also permits OVER () when ordering isn't
required. Optional frames require an ORDER BY clause. Grouped queries only identifies
grouping metadata helpers rather than ordinary aggregate functions.
| Function | Aggregate form | Window form (OVER([PARTITION BY])) | ORDER BY in OVER | ROWS / RANGE |
|---|---|---|---|---|
COUNT, COUNT_BIG, SUM, MIN, MAX, AVG, PRODUCT |
Supported | Supported | Optional | Optional |
VAR, VARP, STDEV, STDEVP |
Supported | Supported | Optional | Optional |
ANY_VALUE, CHECKSUM_AGG |
Supported | Supported | Optional | Optional |
MEDIAN, QUANTILE, PERCENTILE_CONT, PERCENTILE_DISC, APPROX_MEDIAN, APPROX_QUANTILE |
Supported | Supported | Not supported | Not supported |
ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST, LAG, LEAD |
Not supported | Supported | Required | Not supported |
FIRST_VALUE, LAST_VALUE |
Not supported | Supported | Required | Optional |
APPROX_COUNT_DISTINCT, APPROX_PERCENTILE_CONT, APPROX_PERCENTILE_DISC, STRING_AGG, JSON_ARRAYAGG, JSON_OBJECTAGG |
Supported | Not supported | N/A | N/A |
GROUPING, GROUPING_ID |
Grouped queries only | Not supported | N/A | N/A |