In this article

Use this Snap to add aggregate functions such as COUNT, SUM, MIN, and MAX along with the GROUP BY clause in the incoming SQL query. GROUP BY clauses are used to group table records based on columns that contain classifying data and are optional in this Snap. This Snap also allows you to preview the result of the output query. You can validate the modified query using this preview functionality. The supported aggregate functions vary based on the account configuration. See the description of the Aggregate Function field in the Snap Settings section for details.

Prerequisites

None.

Limitations

...

Multiexcerpt include macro

name	ME_ELT_KI_Underscore_Refs
page	ELT Snap Pack

Snap Input and Output

...

Document

...

Min: 1
Max: 1

...

ELT Select
ELT Transform

...

When running without Sub-Query Pushdown Optimization (SPDO), ELT Pipelines that contain an ELT Aggregate Snap and configured with one or more GROUP BY ROLLUP fields, do not verify the column data types while inserting the Snap output values in the target table. This may lead to incorrect data written to the target table. However, as long as SPDO is on, the same Pipeline runs without this issue.

Snap Input and Output

Input/Output	Type of View	Number of Views	Examples of Upstream and Downstream Snaps	Description
Input	Document	Min: 1 Max: 1	ELT Insert- Select ELT LimitTransform	The modified SQL query with in which to add the aggregate functions and the optional GROUP BY clause.

...

Output

Parameter Name

Data Type

Description

Default Value

Example

Label

String

Insert excerpt

File Writer

nopanel

true

ELT Aggregate

Aggregate Revenue

Get preview data

Checkbox

Multiexcerpt include macro

name	getpreviewdata
page	ELT Intersect

Not selected

Selected

General Aggregate Functions List

This field set enables you to specify the columns for which to add the aggregate functions. Each aggregate function must be specified as a new row. Click Image Removed to add a row.

This field set contains the following fields:

Aggregate Function
Field Name
Eliminate duplicates
Alias Name

Aggregate Function

String/Expression/Suggestion

Required. Select the aggregate function to use. Click Image Removed to retrieve the list of the supported aggregate functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details.

Alternatively, you can also enter the name of the aggregate function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error.

You can use the following aggregate functions:

AVG

Document

Min: 1
Max: 1

ELT Insert-Select
ELT Limit

The modified SQL query with the aggregate functions and GROUP BY clause.

Snap Settings

Info

title	SQL Functions and Expressions for ELT

You can use the SQL Expressions and Functions supported for ELT to define your Snap or Account settings with the Expression symbol = enabled, where available. This list is common to all target CDWs supported. You can also use other expressions/functions that your target CDW supports.

Select to apply DISTINCT to the column specified in the Field field. This means that the aggregate function is applied only to the unique values in the column.

Parameter Name

Data Type

Description

Default Value

Example

Label

String

Insert excerpt

	File Writer
	File Writer
nopanel	true

ELT Aggregate

Aggregate Revenue

Get preview data

Checkbox

Multiexcerpt include macro

name	getpreviewdata
page	ELT Intersect

Not selected

Selected

General Aggregate Functions List

This field set enables you to specify the columns for which to add the aggregate functions. Each aggregate function must be specified as a new row. Click Image Added to add a row.

This field set contains the following fields:

Aggregate Function
Field Name
Eliminate duplicates
Alias Name

Aggregate Function

String/Expression/Suggestion

Required. Select the aggregate function to use. Click Image Added to retrieve the list of the supported aggregate functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details.

Alternatively, you can also enter the name of the aggregate function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error.

You can use the following aggregate functions:

AVG
COUNT
COUNT_IF
MAX
MIN
SUM
SKEW
MODE
KURTOSIS

Additionally, the following aggregate functions are available based on the database type:

Snowflake	Redshift	Azure Synapse
ANY_VALUE BITAND_AGG BITOR_AGG BITXOR_AGG BOOLAND_AGG BOOLOR_AGG BOOLXOR_AGGKURTOSIS MEDIAN MODE SKEW STDDEV STDDEV_POP STDDEV_SAMP VAR_POP VAR_SAMP	APPROXIMATE.COUNT BIT_AND BIT_OR BOOL_AND BOOL_OR MEDIAN STDDEV_POP STDDEV_SAMP VAR_POP VAR_SAMP	APPROX_COUNT_DISTINCT COUNT_BIG GROUPING STDEV STDEVP VAR VARP
Databricks Lakehouse Platform		BigQuery
ANY BIT_OR BIT_XOR BOOL_AND BOOL_OR COLLECT_LIST COLLECT_SET EVERY FIRST_VALUE_RESPECT_NULLS FIRST_VALUE_IGNORE_NULLS LAST_VALUE_RESPECT_NULLS LAST_VALUE_IGNORE_NULLS SKEWNESS STDDEV_POP STDDEV_SAMP VAR_POP VAR_SAMP		ANY_VALUE BIT_AND BIT_OR BIT_XOR LOGICAL_AND LOGICAL_OR

See Snowflake Aggregate Functions, Redshift SQL Functions Reference, Aggregate Functions (Transact-SQL)

COLLECT_SET
EVERY
FIRST_VALUE_RESPECT_NULLS
FIRST_VALUE_IGNORE_NULLS
LAST_VALUE_RESPECT_NULLS
LAST_VALUE_IGNORE_NULLS
SOME
SKEWNESS
STDDEV
STDDEV_POP
STDDEV_SAMP
VAR_POP
VAR_SAMP

ANY_VALUE
BIT_AND
BIT_OR
BIT_XOR
LOGICAL_AND
LOGICAL_OR

See Snowflake Aggregate Functions, Redshift SQL Functions Reference, Aggregate Functions (Transact-SQL), Databricks Built-in Functions Reference, or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.

N/A

AVG

Field Name

String/Expression

Required. Select the column on which to apply the aggregate function.

N/A

REVENUE

Eliminate duplicates

Check box

Select to apply DISTINCT to the column specified in the Field field. This means that the aggregate function is applied only to the unique values in the column.

Note

title	Behavior with COUNT_IF aggregate function

Selecting this checkbox while using COUNT_IF aggregate function does not eliminate duplicate records in case of Snowflake and BigQuery databases, as there is no native support. However, it eliminates the duplicates from the list of records when used with a Redshift, Azure Synapse, or Databricks Lakehouse Platform (DLP) instance.

Not selected

Selected

Alias Name

String

Required. Specify the column in which to display the result of the aggregate function. You can also reference this name in downstream Snaps to process the data further.

N/A

TOTAL_REVENUE

Linear Regression Aggregate Functions List

This field set enables you to specify the columns for which to apply the linear regression aggregate functions. Each function must be specified as a new row. Click Image Added to add a row.

This field set contains the following fields:

Aggregate Function
First Field
Second Field
Alias Name

Aggregate Function

String/Expression/Suggestion

Required. Select the aggregate function to use. Click Image Added to retrieve the list of the supported linear regression aggregate functions. The list displays the functions supported by the database. An error is displayed if an account is not configured. See the Troubleshooting section for details.

Alternatively, you can also enter the name of an aggregate function; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error.

You can use the following aggregate functions:

CORR
COVAR_POP
COVAR_SAMP
REGR_AVGX
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
MINHASH (for Snowflake only)
OBJECT_AGG (for Snowflake only)

See Snowflake Aggregate Functions, Databricks Built-in Functions Reference, or BigQuery Aggregate Functions in Standard SQL

for

for more information on the respective aggregate function.

Info

title	Though not supported natively

BigQuery does not natively support the above list of Linear Regression Aggregate Functions. However, SnapLogic provides you with the ability to use these functions with BigQuery through a series of internal query rewrites.

N/A

AVGCORR

First Field

Name

String/Expression

Required. Select Specify the first column on which to apply the aggregate function.

N/A

REVENUE

Eliminate duplicates

Check box

Note

title	Behavior with COUNT_IF aggregate function

Selecting this checkbox while using COUNT_IF aggregate function does not eliminate duplicate records in case of Snowflake and BigQuery databases, as there is no native support. However, it eliminates the duplicates from the list of records when used with a Redshift, Azure Synapse, or Databricks Lakehouse Platform (DLP) instance.

Not selectedSelectedlinear regression aggregate function.N/AREVENUE_LOC1

Second Field

String/ExpressionRequired. Specify the second column on which to apply the linear regression aggregate function.N/AREVENUE_LOC2Alias NameStringRequired. Specify Specify the column in which to display the result of the aggregate function. You can also reference this name in downstream Snaps to process the data further. N/ATOTALLINREGAGG_REVENUELinear Regression Aggregate Functions List Aggregate Concatenation Functions List (Not valid for Databricks Lakehouse Platform)

This field set enables you to specify the

columns for which to apply the linear regression aggregate functions.

list of aggregate concatenation functions to be used for aggregation. The list displays the functions supported by the database that you select in the Account settings. Each function must be specified as a new row. Click Image Modified to add a row.

This field set contains the following fields:

Aggregate Concatenation Function

First

Field

Second Field

Name
Alias Name
Delimiter

Aggregate Concatenation Function

String/Expression/SuggestionRequired. Specify the first column on which to apply the linear regression

Required. Select the aggregate concatenation function to use. Click to retrieve the list of the supported linear regression aggregate functions. The list displays the functions supported by the databasedatabase that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details.

Alternatively, you can also enter the name of an the aggregate concatenation function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error.

You can use the following aggregate functions:

CORR

Snowflake

COVAR_POP

COVAR_SAMP

REGR_AVGX

REGR_AVGY

REGR_COUNT

REGR_INTERCEPT

REGR_R2

REGR_SLOPE

REGR_SXX

REGR_SXY

REGR_SYY

MINHASH (for Snowflake only)

OBJECT_AGG (for Snowflake only)

See Snowflake Aggregate Functions, Databricks Built-in Functions Reference, or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.

Info

title	Though not supported natively

BigQuery does not natively support the above list of Linear Regression Aggregate Functions. However, SnapLogic provides you with the ability to use these functions with BigQuery through a series of internal query rewrites.

N/ACORR

First Field

String/Expression

Redshift	Azure Synapse
LISTAGG LISTAGG_DISTINCT ARRAY_AGG ARRAY_AGG_DISTINCT	LISTAGG DISTINCT_LISTAGG	STRING_AGG
BigQuery
ARRAY_AGG ARRAY_AGG_DISTINCT ARRAY_CONCAT_AGG STRING_AGG STRING_AGG_DISTINCT

See Snowflake Aggregate Functions, Redshift SQL Functions Reference, Aggregate Functions (Transact-SQL), or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.

N/AREVENUE_LOC1LISTAGG

Second Field Name

String/ExpressionRequired. Specify Specify the second column on which to name to apply the linear regression aggregate concatenation function.N/AREVENUENEW_LOC2LOCATIONS

Alias Name

StringRequired. Specify the column the column in which to display the result of the aggregate function. You can also reference this name in downstream Snaps to process the data further.N/ALINREGAGG_REVENUEAggregate Concatenation Functions List (Not valid for Databricks Lakehouse Platformconcatenation function. You can also reference this name in downstream Snaps to process the data further.N/AUNIQUE_LOCS_LIST

Delimiter

String/ExpressionSpecify the delimiting character (string constant) to be used to separate the concatenated values., (comma); (semi-colon)Percentile Distribution Functions List (Not valid for Azure Synapse, Databricks Lakehouse Platform, BigQuery)

This field set enables you to specify the list of aggregate concatenation of percentile distribution functions to be used for aggregation. The list displays the functions supported by the database that you select in the Account settings. Each function must be specified as a new row. Click to add a row.

This field set contains the following fields:

Aggregate Concatenation Percentile Distribution Function
Field NamePercentile
Alias Name
Delimiter

Aggregate Concatenation Percentile Distribution Function

String/Expression/Suggestion

Required. Select the aggregate concatenation function the percentile distribution function to use. Click to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details.

Alternatively, you can also enter the name of the aggregate concatenation function to percentile distribution function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error.

You can use the following aggregate functions:

Snowflake	Redshift

Azure Synapse

LISTAGG
LISTAGG_DISTINCT
ARRAY_AGG
ARRAY_AGG_DISTINCT

LISTAGG
DISTINCT_LISTAGG

STRING_AGG

BigQuery

ARRAY_AGG
ARRAY_AGG_DISTINCT
ARRAY_CONCAT_AGG
STRING_AGG
STRING_AGG_DISTINCT

See Snowflake Aggregate Functions, Redshift SQL Functions Reference, Aggregate Functions (Transact-SQL), or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.

N/ALISTAGG

Field Name

String/ExpressionRequired. Specify the column name to apply the concatenation function.N/ANEW_LOCATIONS

Alias Name

StringRequired. Specify the column in which to display the result of the concatenation function. You can also reference this name in downstream Snaps to process the data further.N/AUNIQUE_LOCS_LIST

Delimiter

String/ExpressionSpecify the delimiting character (string constant) to be used to separate the concatenated values., (comma); (semi-colon)Percentile Distribution Functions List (Not valid for Azure Synapse, Databricks Lakehouse Platform, BigQuery)

This field set enables you to specify the list of percentile distribution functions to be used for aggregation. The list displays the functions supported by the database that you select in the Account settings. Each function must be specified as a new row. Click Image Removed to add a row.

This field set contains the following fields:

Percentile Distribution Function
Percentile
Alias Name

Percentile Distribution Function

String/Expression/Suggestion

Required. Select the percentile distribution function to use. Click Image Removed to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details.

Alternatively, you can also enter the name of the percentile distribution function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error.

You can use the following aggregate functions:

SnowflakeRedshift

PERCENTILE_DISC
PERCENTILE_CONT

APPROXIMATE_PERCENTILE_DISC
PERCENTILE_CONT

See Snowflake Aggregate Functions or Redshift SQL Functions Reference for more information on the respective aggregate function.

N/A

PERCENTILE_DISC

Percentile String/ExpressionRequired. Specify the percentile value to be considered for applying the percentile distribution function.N/A0.8 (80th percentile)Alias NameStringRequired. Specify the column in which to display the result of the percentile distribution function. You can also reference this name in downstream Snaps to process the data further.N/AREVENUE_CPERCENTILEGROUP BY Fields ListThis field set enables you to specify the columns for which to use the GROUP BY clause. Each column


PERCENTILE_DISC PERCENTILE_CONT	APPROXIMATE_PERCENTILE_DISC PERCENTILE_CONT

See Snowflake Aggregate Functions or Redshift SQL Functions Reference for more information on the respective aggregate function.

N/A

PERCENTILE_DISC

Percentile String/ExpressionRequired. Specify the percentile value to be considered for applying the percentile distribution function.N/A0.8 (80th percentile)Alias NameStringRequired. Specify the column in which to display the result of the percentile distribution function. You can also reference this name in downstream Snaps to process the data further.N/AREVENUE_CPERCENTILEGroup By All The Input ColumnsCheckboxSelect this checkbox to group the output data by all columns in the input table. This disables the Group By Arguments List fieldset.Not SelectedSelectedGROUP BY Arguments List

This fieldset enables you to specify the columns for which to use the GROUP BY clause. Each column must be specified in a new row. Click Image Added to add a row.

This fieldset contains the following field:

Argument

ArgumentString/ExpressionSpecify the column in which to add the GROUP BY clause.N/A

GRADE

GENDER

GROUP BY CUBECheckbox

Select this checkbox to group the output data using the GROUP BY CUBE clause. This activates the GROUP BY CUBE Arguments List fieldset.

Not SelectedSelectedGROUP BY CUBE Arguments List

This fieldset enables you to specify the arguments for the GROUP BY CUBE clause. In addition to the GROUP BY ROLLUP, GROUP BY CUBE adds all the “cross-tabulations” rows and is equivalent to a series of grouping sets. Each argument must be specified in a new row. Click Image Added to add a row.

This fieldset contains the following field:

Argument

ArgumentString/ExpressionSpecify the column in which to add the GROUP BY CUBE clause.N/A

CUST_ID

ORDERS

GROUP BY ROLLUPCheckbox

Select this checkbox to group the output data using the GROUP BY ROLLUP clause. This activates the GROUP BY ROLLUP Arguments List fieldset.

Not SelectedSelectedGROUP BY ROLLUP Arguments List

This fieldset enables you to specify the arguments for the GROUP BY ROLLUP clause that produces sub-total rows (in addition to the grouped rows). Each argument must be specified in a new row. Click Image Added to add a row.

This fieldset contains the following field:

Argument

ArgumentString/ExpressionSpecify the column in which to add the GROUP BY ROLLUP clause.N/A

CATEGORY

REGION

GROUP BY GROUPING SETSCheckbox

Select this checkbox to group the output data using the GROUP BY GROUPING SETS clause. This activates the GROUP BY GROUPING SETS Arguments List fieldset.

Not SelectedSelectedGROUP BY GROUPING SETS Arguments List

This fieldset enables you to specify the arguments for the GROUP BY GROUPING SETS clause that that allows computing multiple GROUP BY clauses in a single statement. The group set is a set of dimension columns. Each argument must be specified in a new row. Click Image Added to add a row.

This fieldset contains the following field:

Argument

ArgumentString/ExpressionSpecify the column in which to add the GROUP BY GROUPING SETS clause.N/A

CATEGORY

REGION

HAVING Predicate ListSpecify the column in which to add the GROUP BY clause

This fieldset enables you to specify the arguments for the HAVING predicates (conditions) over the result of applying a GROUP BY clause. Each predicate must be specified in a new row. Click + Image Added to add a row.

This field set fieldset contains the following field:

GROUP BY Field

GROUP BY FieldString/Expression

Predicate
Boolean Operator

PredicateString/ExpressionSpecify the predicate (condition) to filter the results of a GROUP BY (CUBE/ROLLUP/GROUPING SETS) operation.N/AORD_COUNT(*) >100Boolean OperatorDrop-down listSelect one boolean operator to apply another HAVING predicate in combination with the predicate selected.N/A

GRADE

GENDERANDORDER-BY Fields (Aggregate Concatenation Functions Only)

This field set enables you to specify the columns by which to sort the output data set. Each column must be specified in a new row. Click + to add a row.

This field set contains the following fields:

ORDER BY Field
Sort Order
Null Value Sort Preference

ORDER BY FieldString/ExpressionSpecify the column by which to sort the output data set.N/A

GRADE

GENDER

Sort OrderDrop-down listChoose one of the possible sort orders - ASC (ascending) or DESC (descending) for the output data set.ASCDESCNull Value Sort PreferenceDrop-down listChoose where in the sort order do the null values, if any, in the ORDER BY Field be placed - NULLS FIRST (at the beginning) or NULLS LAST (at the end)NULLS FIRSTNULLS LAST

...

Versions Compared

Old Version 23

New Version 31

Key

Prerequisites

Limitations

Snap Input and Output

Snap Input and Output

Snap Settings

Page Comparison

Versions Compared

Old Version 23

New Version 31

Key

Prerequisites

Limitations

Snap Input and Output

Snap Input and Output

Snap Settings