ARRAY_AGG
Ordinary array aggregation:
>____ARRAY_AGG(expression_____________________________________________)__> ǀ <_,________________________ ǀ ǀ _ASC___ ǀ ǀ ǀ_ORDER BY_sort-key__ǀ_______ǀ____ǀ_ǀ ǀ_DESC__ǀ
sort-key-expression
>_______column-name_______________________________________________________>< ǀ_expression_ǀ
Associative array aggregation:
>__ARRAY_AGG(index-expression, expression)________________________________>
AVG
_ALL______ >__AVG(_ǀ__________ǀ_numeric-expression)__________________________________> ǀ_DISTINCT_ǀ
CORRELATION
>_CORRELATION(expression-1, expression-2)_________________________________>
COUNT
_ALL_______ >_COUNT(__ǀ__________ǀ__expression___)____________________________________> ǀ ǀ_DISTINCT_ǀ ǀ ǀ_*________________________ǀ
COUNT_BIG
_ALL_______ >_COUNT_BIG(___ǀ__________ǀ__expression___)_______________________________> ǀ ǀ_DISTINCT_ǀ ǀ ǀ_*ǀ_______________________ǀ
COVARIANCE or COVARIANCE_SAMP
>____COVARIANCE_______(expression-1, expression-2)________________________> ǀ_COVARIANCE_SAMP_ǀ
GROUPING
>__GROUPING(expression____________________________________________________>
LISTAGG
_ALL______ >_LISTAGG(___ǀ__________ǀ_string-expression_______________)_______________> ǀ_DISTINCT_ǀ ǀ_,_separator_ǀ >_________________________________________________________________________> ǀ <_,________________ ǀ ǀ _ASC__ ǀ ǀ ǀ_WITHIN GROUP (_ORDER BY_sort-key____ǀ_______ǀ___)_ǀ_ǀ ǀ_DESC__ǀ
MAX
_ALL______ >__MAX(_ǀ__________ǀ_expression)_________________________________________> ǀ_DISTINCT_ǀ
MEDIAN
>____MEDIAN(numeric-expression)__________________________________________>
MIN
_ALL______ >__MIN(_ǀ__________ǀ_expression)_________________________________________> ǀ_DISTINCT_ǀ
PERCENTILE CONT
>__PERCENTILE_CONT(percentile-expression)________________________________> _ASC___ >__WITHIN GROUP (_ORDER BY_sort-expression)___ǀ_______ǀ_________)________> ǀ_DESC__ǀ
STDDEV
_ALL______ >__STDDEV(_ǀ__________ǀ_expression)______________________________________> ǀ_DISTINCT_ǀ
STDDEV_SAMP
_ALL______ >__STDDEV_SAMP(_ǀ__________ǀ_expression)_________________________________> ǀ_DISTINCT_ǀ
SUM
_ALL______ >__SUM(_ǀ__________ǀ_expression)_________________________________________> ǀ_DISTINCT_ǀ
VARIANCE or VARIANCE SAMP
_ALL______ >_____VARIANCE_______(_ǀ__________ǀ_expression)__________________________> ǀ_VARIANCE_SAMP_ǀ ǀ_DISTINCT_ǀ
XMLAGG
>____XMLAGG(XML-expression____________________________________________)__> ǀ <_,______________________ ǀ ǀ _ASC___ ǀ ǀ ǀ_ORDER BY_sort-key__ǀ_______ǀ ǀ_ǀ ǀ_DESC__ǀ
sort-key
>________column-name______________________________________________________>< ǀ_expression_ǀ
Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at blogs@bmc.com. Db2 is a registered trademark of the IBM Corporation.
