Mainframe Blog

Aggregate Functions in Db2 12

< 1 minute read
Stephen Watts

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)________________________________>

(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)

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. This collateral helps Db2 for z/OS users with daily activities in administering and programming for Db2 on z/OS.


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.

BMC Brings the A-Game

BMC works with 86% of the Forbes Global 50 and customers and partners around the world to create their future. With our history of innovation, industry-leading automation, operations, and service management solutions, combined with unmatched flexibility, we help organizations free up time and space to become an Autonomous Digital Enterprise that conquers the opportunities ahead.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) contributes to a variety of publications including, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.