ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL Expressions sql_exp →  sql_exp - sql_win →  ABAP SQL - Examples of Window Expressions 

SQL Expressions, Window Expressions with Grouping

This example demonstrates window expressions for a grouped results set.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA(out) = cl_demo_output=>new( ).

    SELECT char1 && '_' && char2 AS group,
           COUNT(*)                   AS cnt,
           AVG( num1 AS DEC( 12,2 ) ) AS avg,
           SUM( num1 )                AS sum,
           COUNT(*)           OVER( PARTITION BY char1 ) AS win_cnt,
           ROW_NUMBER( )      OVER( PARTITION BY char1 ) AS win_num,
           MIN( AVG( num1 AS DEC( 12,2 ) ) )
                             OVER( PARTITION BY char1 ) AS win_min,
           MAX( AVG( num1 AS DEC( 12,2 ) ) )
                             OVER( PARTITION BY char1 ) AS win_max,
           SUM( SUM( num1 ) ) OVER( PARTITION BY char1 ) AS win_sum,
           division( 100 * SUM( num1 ),
                     SUM( SUM( num1 ) ) OVER( PARTITION BY char1 ),
                     2 ) AS win_perc
           FROM demo_expressions
           GROUP BY char1, char2
           ORDER BY group
           INTO TABLE @DATA(groups_windowed).

    out->display( groups_windowed ).

Description

The example shows the combination of window expressions in the SELECT list with a GROUP BY clause. In this case, aggregate functions used as a window function can also have aggregate functions as arguments.

The GROUP BY groups by the columns CHAR1 and CHAR2. In the results set created by this, OVER is used to define windows whose window criterion is the column CHAR1. Aggregate expressions used to specify columns in the SELECT list (due to the GROUP BY clause) are also used as arguments of window functions.

The aggregate functions used as an argument of window functions would not need to appear as separately specified columns in the SELECT list, but are shown here anyway to highlight how these values are produced.