Aggregate Functions in Db2 12

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.