TL;DR Summary: Aggregate Functions in Db2 12 enhance data analysis with tools like AVG, COUNT, and SUM, facilitating statistical calculations for improved data management. These functions support various aggregations for comprehensive insights.
- Functions enable statistical analysis, including ordinary and associative array aggregations.
- Key functions include AVG, COUNT, and SUM for comprehensive data insights.
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.
Db2 Guide
- Db2 12 for z/OS: An Introduction
- Scalar Functions in Db2 12
- How to Read IBM<sup>®</sup> Syntax Diagrams for Db2 12
- Language Elements in Db2 12
- Aggregate Functions in Db2 12
- Table & Row Functions in Db2 12
- Queries in Db2 12
- Commands in Db2 12
- SQL Control Statements in Db2 12
- Explain Tables in Db2 12
- DSNZPARMs in Db2 12
- Bind Parameters in Db2 12
- Db2 Limits in Db2 12
- SQL Communication Area (SQLCA) in Db2 12
- REXX SQLCA in Db2 12
- Get Diagnostics in Db2 12
- IFCIDS in Db2 12
- Catalog Tables in Db2 12
- Exceptions in Db2 12
- XML Schema Repository Tables in Db2 12
- Updatable Catalog Statistics in Db2 12
- IBM Utilities in Db2 12
- SQL Positive Return Codes in Db2 12
- SQL Error Return Codes in Db2 12
These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.