Preface
Function reference
- Function components
Constants
Operators
Dates
Functions
- Function overview
  - Aggregate functions
    - Filter conditions
  - Conversion functions
  - Data cleansing functions
  - Date functions
  - Encoding functions
  - Financial functions
  - Horizontal expansion functions
  - Numeric functions
  - Scientific functions
  - Special functions
  - String functions
  - Test functions
  - Window functions
    - Aggregate functions as window functions
    - Nested aggregate functions as window functions
- Function quick reference
- Functions in advanced mode
- %OPR_CONCAT%
- %OPR_CONCATDELIM%
- %OPR_IIF%
- %OPR_SUM%
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- AES_GCM_DECRYPT
- AES_GCM_ENCRYPT
- ASCII
- AVG
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- IS_DATE
- IS_NUMBER
- IS_SPACES
- ISNULL
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SETCOUNTVARIABLE
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- VARIANCE
System variables
Datatype reference

Function Reference

Back Next

Aggregate functions

Aggregate functions return summary values for non-null values in selected fields.

With aggregate functions you can complete the following tasks:

Calculate a single value for all rows in a group.

Return a single value for each group in an Aggregator object.

Apply filters to calculate values for specific rows in the selected fields.

Use operators to perform arithmetic within the function.

Calculate two or more aggregate values derived from the same source columns in a single pass.

Use aggregate functions in

mapping

tasks only.

The transformation language includes the following aggregate functions:

AVG

COUNT

FIRST

LAST

MAX (Date)

MAX (Number)

MAX (String)

MEDIAN

MIN (Date)

MIN (Number)

MIN (String)

PERCENTILE

STDDEV

SUM

VARIANCE

Use aggregate functions in Aggregator objects only. You can nest only one aggregate function within another aggregate function.

Data Integration

evaluates the innermost aggregate function expression and uses the result to evaluate the outer aggregate function expression.

You cannot nest aggregate functions in advanced mode.

You can set up an Aggregator object that groups by ID and nests two aggregate functions as follows:

SUM( AVG( earnings ) )

where the dataset contains the following values:

ID	EARNINGS
1	32
1	45
1	100
2	65
2	75
2	76
3	21
3	45
3	99

The return value is 186.

Data Integration

groups by ID, evaluates the AVG expression, and returns three values. Then it adds the values with the SUM function to get the result.

Function overview

Filter conditions

Download Guide

Watch

Comments

Cloud Data Integration Homepage

0 COMMENTS

We’d like to hear from you! Log in to comment.

Rename Saved Search

Table of Contents

Function Reference

Function Reference

Aggregate functions

Aggregate functions