Aggregate functions
Overview
An aggregate function performs a calculation on a set of values, and returns a single value. Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
The aggregate functions covered in this section are:
- AVG
- APPROX_COUNT_DISTINCT
- CHECKSUM_AGG
- COUNT
- COUNT_BIG
- GROUPING
- GROUPING_ID
- MAX
- MIN
- STDEV
- STDEVP
- STRING_AGG
- SUM
- VAR
- VARP
The clauses covered in this section are:
AVG
AVG returns the average of the values in a group. It ignores null values.
This function can be used in conjunction with the OVER clause to become a window function.
Return Type
int
Syntax
AVG ([ColumnName])
Example 1
This example returns the average of the values in the [Quantity] column of the [Product].[Tasks] table.
SELECT AVG([Quantity])
FROM [Product].[Tasks]
WHERE [Deleted] IS NULL
APPROX_COUNT_DISTINCT
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
APPROX_COUNT_DISTINCT returns the approximate number of non-null values in a group.
Return Type
bigint
Syntax
APPROX_COUNT_DISTINCT(expression)
Arguments
| Argument | Description | Example |
|---|---|---|
expression | A column or expression. | [Quantity] |
Example 1
This example counts the number of non-null values present in the [Quantity] column of the [Documentation].[Customer] table.
SELECT APPROX_COUNT_DISTINCT([Quantity])
FROM [Documentation].[Customer]
CHECKSUM_AGG
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
Checksums return a value that indicate whether or not the input value has changed over time
CHECKSUM_AGG returns the checksum value of the numeric columns specified by the query, allowing you to detect data changes. It ignores null values.
Return Type
int
Syntax
CHECKSUM_AGG( [ ALL | DISTINCT ] expression)
Arguments
| Argument | Description | Example |
|---|---|---|
ALL | Using the ALL argument forces the query to return the checksum of all values in the expression, including duplicates. | |
DISTINCT | Using the DISTINCT argument forces the query to calculate only on distinct (non-duplicate) values. | |
expression | A column or expression containing integer values. Note that you can use the CAST function to cast float values as int values where needed. | Quantity |
Example 1
This example returns the checksum value on the 'Quantity' column in the [Documentation].[Customer] table. Note that it first casts the 'Quantity' column into an integer for the calculation.
SELECT CHECKSUM_AGG(CAST(Quantity AS INT))
FROM [Documentation].[Customer]
Example 2
This example is the same as Example 1, but it uses the DISTINCT argument to calculate only on non-duplicate values.
SELECT CHECKSUM_AGG(DISTINCT CAST(Quantity AS INT))
FROM [Documentation].[Customer]
COUNT
COUNT returns the number of items found in a group. It differs from the COUNT_BIG function in that it returns int values.
This function can be used in conjunction with the OVER clause to become a window function.
Return Type
int
Syntax
COUNT( [ ALL | DISTINCT ] expression )
Arguments
| Argument | Description | Example |
|---|---|---|
ALL | Using the ALL argument returns all values in the expression. | |
DISTINCT | Using the DISTINCT argument returns only unique, non-null values. | |
expression | A column or expression containing numerical values. | Quantity |
Example 1
This example calculate the number of items (rows) found in the 'Quantity' column of the [Documentation].[Customer] table.
SELECT COUNT(ALL 'Quantity')
FROM [Documentation].[Customer]
COUNT_BIG
COUNT_BIG returns the number of items found in a group. It differs from the COUNT function in that it returns bigint values.
Syntax
COUNT_BIG( [ ALL | DISTINCT ] expression )
Return Type
bigint
Arguments
| Argument | Description | Example |
|---|---|---|
ALL | Using the ALL argument returns all values in the expression. | |
DISTINCT | Using the DISTINCT argument returns only unique, non-null values. | |
expression | A column or expression containing numerical values. | Quantity |
Example
This example calculate the number of items (rows) found in the 'Quantity' column of the [Documentation].[Customer] table.
SELECT COUNT_BIG(ALL 'Quantity')
FROM [Documentation].[Customer]
GROUPING
GROUPING indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set.
Return Type
tinyint
Syntax
GROUPING(expression)
Arguments
| Argument | Description |
|---|---|
expression | A column or expression that contains a column in a GROUP BY clause. |
Example 1
The following example groups the 'Customer ID' column and aggregates the 'Quantity' column in [Documentation].[Customer] table. The GROUPING function is applied to 'Customer ID' column.
SELECT [Customer ID], SUM([Quantity]) 'Total', GROUPING([Customer ID]) AS 'Grouping'
FROM [Documentation].[Customer]
GROUP BY [Customer ID];
GROUPING_ID
GROUPING_ID computes the level of grouping.
Return Type
int
Syntax
The GROUPING_ID expression must exactly match the expression in the GROUP BY list. For example, if you are grouping by DATEPART (yyyy, column name), use GROUPING_ID (DATEPART (yyyy, column name)); or if you are grouping by column name, use GROUPING_ID (column name).
For further details about the GROUPING_ID function, as well as examples,please review the documentation here
GROUPING_ID(expression)
Arguments
| Argument | Description | Example |
|---|---|---|
expression | A column or expression in a GROUP BY clause. | Quantity |
MIN
MIN returns the smallest value within a group. It ignores null values.
This function can be used in conjunction with the OVER clause to become a window function.
Return Type
int
Syntax
SELECT MIN(Column)
FROM [Domain].[Table];
Example 1
This example returns the smallest value from the [Quantity] column in the [Customers] table.
SELECT MIN(Quantity)
FROM [Documentation].[Customer]
MAX
MAX returns the largest value within a group. It ignores null values.
This function can be used in conjunction with the OVER clause to become a window function.
Return Type
int
Syntax
SELECT MAX(Column)
FROM [Domain].[Table];
Example 1
This example returns the largest value from the [Quantity] column in the [Customers] table.
SELECT MAX(Quantity)
FROM [Documentation].[Customer]
STDEV
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
STDEV returns the statistical standard deviation of all values in the specified expression.
Return Type
float
Syntax
STDEV( [ ALL | DISTINCT ] expression )
Arguments
| Argument | Description | Example |
|---|---|---|
ALL | Using the ALL argument applies the function to all values in the expression. | |
DISTINCT | Using the DISTINCT argument returns only unique, non-null values. | |
expression | A column or expression containing numeric values. | Quantity |
Example 1
This example casts and calculates the standard deviation of the 'Quantity' column of the [Documentation].[Customer] table.
SELECT STDEV(CAST (Quantity as INT))
FROM [Documentation].[Customer]
STDEVP
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
STDEVP returns the statistical standard deviation for the population of all values in the specified expression.
Return Type
float
Syntax
STDEVP( [ ALL | DISTINCT ] expression )
Arguments
| Argument | Description | Example |
|---|---|---|
ALL | Using the ALL argument applies the function to all values in the expression. | |
DISTINCT | Using the DISTINCT argument returns only unique, non-null values. | |
expression | A column or expression containing numeric values. | Quantity |
Example 1
This example casts and calculates the population standard deviation of the 'Quantity' column of the [Documentation].[Customer] table.
SELECT STDEVP(CAST (Quantity as INT))
FROM [Documentation].[Customer]
STRING_AGG
STRING_AGG concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string.
Return Type
The return type depends on the data type of your expression:
| Input | Result |
|---|---|
| NVARCHAR(MAX) | NVARCHAR(MAX) |
| VARCHAR(MAX) | VARCHAR |
| int, bigint, smallint, tinyint, numeric, float, real, bit, decimal, smallmoney, money, datetime, datetime2 | NVARCHAR(4000) |
Syntax
STRING_AGG( expression, separator)
Arguments
| Argument | Description | Example |
|---|---|---|
expression | A column or expression of any data type. Expressions are converted to NVARCHAR or VARCHAR types during concatenation. Non-string types are converted to NVARCHAR type. | |
separator | A NVARCHAR or VARCHAR column or expression that will be used as a separator for concatenated strings. | ',' |
Example 1
This example concatenates the values of the 'Country' column of the [Documentation].[Customer] table and separates them by a comma value.
SELECT STRING_AGG( Country, ',')
FROM [Documentation].[Customer]
SUM
SUM returns the sum of numeric values. Null values are ignored.
This function can be used in conjunction with the OVER clause to become a window function.
Return Type
Returns type will match the expression values to the most precise expression type.
Syntax
SUM( [ ALL | DISTINCT ] expression )
Arguments
| Argument | Description | Example |
|---|---|---|
ALL | Using the ALL argument applies the function to all values in the expression. | |
DISTINCT | Using the DISTINCT argument returns only unique, non-null values. | |
expression | A column or expression containing numeric values. | Quantity |
Example 1
This example calculates the sum of the values in the 'Quantity' column of the [Documentation].[Customer] table.
SELECT SUM(ALL(Quantity))
FROM [Documentation].[Customer]
VAR
VAR returns the statistical variance of all values in the specified expression.
Return Type
float
Syntax
VAR( [ ALL | DISTINCT ] expression )
Arguments
| Argument | Description | Example |
|---|---|---|
ALL | Using the ALL argument applies the function to all values in the expression. | |
DISTINCT | Using the DISTINCT argument returns only unique, non-null values. | |
expression | A column or expression containing exact numeric or approximate numeric data type category values, except for the bit data type. | Quantity |
Example 1
This example calculates the variance of the values in the 'Quantity' column of the [Documentation].[Customer] table.
SELECT VAR(ALL(Quantity))
FROM [Documentation].[Customer]
VARP
VARP returns the statistical variance for the population of all values in the specified expression.
Return Type
float
Syntax
VARP( [ ALL | DISTINCT ] expression )
Arguments
| Argument | Description | Example |
|---|---|---|
ALL | Using the ALL argument applies the function to all values in the expression. | |
DISTINCT | Using the DISTINCT argument returns only unique, non-null values. | |
expression | A column or expression containing exact numeric or approximate numeric data type category values, except for the bit data type. | Quantity |
Example 1
This example calculates the population variance of the values in the 'Quantity' column of the [Documentation].[Customer] table.
SELECT VARP(ALL(Quantity))
FROM [Documentation].[Customer]
Clauses
HAVING
The HAVING clause is used to filter the result set based on aggregate functions.
Syntax
SELECT AggFunc([Column]), [Column]
FROM [Domain].[Table]
GROUP BY [Column]
HAVING condition
Example 1
The following example returns the number of customers in each country, but only when a country has more than 1 customer.
SELECT COUNT([Customer ID]), [Country]
FROM [Documentation].[Customer]
GROUP BY [Country]
HAVING COUNT([Customer ID]) > 1
OVER
The OVER clause can be applied to aggregate functions to define a particular window (set of rows).
This can be used with the following functions:
- AVG
- MAX
- MIN
- SUM
- COUNT
Using the OVER clause defines an aggregate function as a window function. A window functionperforms a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.
Syntax
OVER ([PARTITION BY columns] [ORDER BY columns])
Arguments
| Argument | Description | Example |
|---|---|---|
PARTITION BY | This subclause defines the groups into which the rows are divided. | |
ORDER BY | This clause defines the order of the records in the window frame. |
Example 1
In the following example, we use the OVER clause on the AVG function to find the salary of employees for each department, and order the employees within that department by age.
SELECT Name, Age, Department, Salary,
AVG(Salary) OVER( PARTITION BY Department) AS Avg_Salary
FROM [Documentation].[Customer]
