image_pdfimage_print

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.

Order Now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC.
Order Now ›
Last updated: 04/01/2019

These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing blogs@bmc.com.

About the author

Stephen Watts

Stephen Watts

Stephen Watts (Birmingham, AL) has worked at the intersection of IT and marketing for BMC Software since 2012.

Stephen contributes to a variety of publications including CIO.com, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.

Run and Reinvent Your Business with BMC

BMC has unmatched experience in IT management, supporting 92 of the Forbes Global 100, and earning recognition as an ITSM Gartner Magic Quadrant Leader for six years running. Our solutions offer speed, agility, and efficiency to tackle business challenges in the areas of service management, automation, operations, and the mainframe. Learn more about BMC ›