Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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.

Image RemovedImage Added

Prerequisites

None.

...

page
Parameter NameData TypeDescriptionDefault ValueExample 
LabelString

Excerpt Include
File Writer
File Writer
nopaneltrue

ELT AggregateAggregate Revenue
Get preview dataCheckbox
Multiexcerpt include macro
namegetpreviewdata
pageELT Intersect
Not selectedSelected
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 to add a row.

This field set contains the following fields:

  • Aggregate Function
  • Field
  • Eliminate duplicates
  • Alias Name
Aggregate FunctionString/Expression/Suggestion

Required. Select the aggregate function to use. Click  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
  • MAX
  • MIN
  • SUM

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

SnowflakeRedshiftSynapse
  • ANY_VALUE
  • BITAND_AGG
  • BITOR_AGG
  • BITXOR_AGG 
  • BOOLAND_AGG 
  • BOOLOR_AGG
  • BOOLXOR_AGG
  • COUNT_IF
  • KURTOSIS
  • 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
  • BIT_OR
  • BIT_XOR
  • BOOL_AND
  • BOOL_OR
  • COLLECT_LIST
  • COLLECT_SET
  • COUNT_IF
  • 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

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

N/AAVG
FieldString/ExpressionRequired. Select the column on which to apply the aggregate function.N/AREVENUE
Eliminate duplicatesCheck boxSelect 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. Not selectedSelected
Alias NameStringRequired. 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/ATOTAL_REVENUE
Linear Regression Aggregate Functions List (Snowflake only)List 

This field set enables you to specify the columns for which to apply the linear regression aggregate functions. You can use this field set only when the target database is Snowflake.   Each function must be specified as a new row. Click  to add a row.

This field set contains the following fields:

  • Aggregate Function
  • First Field
  • Second Field
  • Alias Name
Aggregate FunctionString/Expression/Suggestion

Required. Select the aggregate function to use. Click  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.SYYMINHASH
  • MINHASH (for Snowflake only)
  • OBJECT.AGGAGG (for Snowflake only)

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

N/ACORR
First Field
String/ExpressionRequired. Specify the first column on which to apply the linear 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_LOC2
Alias NameStringRequired. 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/ALINREGAGG_REVENUE
Aggregate Concatenation Functions ListList (Not valid for Databricks Lakehouse Platform)

This field set enables you to specify the 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  to add a row.

This field set contains the following fields:

  • Aggregate Concatenation Function
  • Field Name
  • Alias Name
  • Delimiter

Aggregate Concatenation Function

String/Expression/Suggestion

Required. Select the aggregate concatenation 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 use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error. 

You can use the following aggregate functions:

SnowflakeRedshiftSynapse
  • LISTAGG
  • LISTAGG_DISTINCT
  • ARRAY_AGG
  • ARRAY_AGG_DISTINCT
  • LISTAGG
  • DISTINCT_LISTAGG
  • STRING_AGG

See Snowflake Aggregate Functions, Redshift SQL Functions Reference or Aggregate Functions (Transact-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

StringSpecify 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 and Databricks Lakehouse Platform)

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  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  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:

SnowflakeRedshiftSynapse
  • PERCENTILE_DISC
  • PERCENTILE_CONT
  • APPROXIMATE_PERCENTILE_DISC
  • PERCENTILE_CONT

None

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 NameStringSpecify 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_CPERCENTILE
GROUP BY Fields List

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

This field set contains the following field:

  • Output Field
Output FieldStringSpecify the column in which to add the GROUP BY clause.N/A

GRADE

GENDER

ORDER-BY Fields

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.ASCDESC
Null 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 LASTGet preview dataCheck box
Multiexcerpt include macro
namegetpreviewdata
ELT IntersectNot selectedSelected

Troubleshooting

Error MessageReasonResolution
Account is required, please set in Accounts tabAccount configuration is mandatory in the ELT Aggregate Snap. An account has not been selected/configured in the Snap.Select or configure an account for the Snap. See Configuring the ELT Snap Pack Accounts for details.
Failure: Invalid Function Specified : <field value>The specified aggregate function is incorrect/not supported. This is likely to occur if you have entered the aggregate function's name in the Aggregate Function field manually.

Check whether the aggregate function specified in the error message is spelled correctly. You can avoid this error by selecting from the Snap's suggested aggregate functions. Click  and select the required aggregate function.

...

We want to perform aggregate functions for the values in the BYTE column. Accordingly, we add the ELT Aggregate Snap and configure it as required. In this example, we want to calculate the COUNT, SUM, MIN, and MAX. So, we configure the ELT Aggregate Snap as shown below:

Image RemovedImage Added

Based on this configuration, the ELT Aggregate Snap builds a query as shown below:

...