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 [email protected]. 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 [email protected].

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
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.