Preface
The Transformation Language
Constants
Operators
Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Binary Functions
  - Character Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Variable Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ASCII
- AVG
- BINARY_COMPARE
- BINARY_CONCAT
- BINARY_LENGTH
- BINARY_SECTION
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DEC_HEX
- DECODE
- DECOMPRESS
- EBCDIC_ISO88591
- ENC_BASE64
- ENC_HEX
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOOKUP
- 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
- SETCOUNTVARIABLE
- SET_DATE_PART
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- VARIANCE
Creating Custom Functions
Custom Function API Reference

Transformation Language Reference

10.4.0
- 10.5.1
- 10.5

Back Next

Aggregate Functions

Aggregate functions return summary values for non-null values in selected ports. With aggregate functions you can:

Calculate a single value for all rows in a group.

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

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

Use operators to perform arithmetic within the function.

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

The transformation language includes the following aggregate functions:

ANY

AVG

COLLECT_LIST

COLLECT_MAP

COUNT

FIRST

LAST

MAX (Date)

MAX (Number)

MAX (String)

MEDIAN

MIN (Date)

MIN (Number)

MIN (String)

PERCENTILE

STDDEV

SUM

VARIANCE

If you configure the

PowerCenter Integration Service

to run in Unicode mode, MIN and MAX return values according to the sort order of the code page you specify in the session properties.

You can use aggregate functions in Aggregator transformations. You can nest only one aggregate function within another aggregate function. The

PowerCenter Integration Service

evaluates the innermost aggregate function expression and uses the result to evaluate the outer aggregate function expression. You can set up an Aggregator transformation 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. The

PowerCenter Integration Service

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

You can also use aggregate functions as window functions in an Expression transformation. To use an aggregate function as a window function when you run a mapping on the Spark engine, you must configure the transformation for windowing. If you use an aggregate function as a window function, the Expression transformation becomes active.

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

Aggregate Functions

Aggregate Functions