ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  SELECT clauses →  SELECT - GROUP BY 

SELECT, Grouping Sets

This example demonstrates the GROUP BY addition GROUPING SETS of the statement SELECT.

Other versions: 7.31 | 7.40 | 7.54

Source Code

   "Example for GROUPING SETS
    SELECT FROM sflight
           FIELDS carrid,
                  connid,
                  planetype,
                  SUM( seatsmax ) AS sum_seatsmax
                  WHERE carrid = 'LH'
           GROUP BY GROUPING SETS ( ( carrid, planetype ),
                                   ( carrid, connid ) )
           ORDER BY connid, planetype
           INTO TABLE @DATA(result_grouping_sets).


   "Example for UNION ALL
    SELECT FROM sflight
           FIELDS carrid,
                  @( VALUE sflight-connid( ) ) AS connid,
                  planetype,
                  SUM( seatsmax ) AS sum_seatsmax
           WHERE carrid = 'LH'
           GROUP BY carrid, planetype
    UNION ALL
    SELECT FROM sflight
           FIELDS carrid,
                  connid,
                  @( VALUE sflight-planetype( ) ) AS planetype,
                  SUM( seatsmax ) AS sum_seatsmax
           WHERE carrid = 'LH'
           GROUP BY carrid, connid
           ORDER BY carrid, connid, planetype, sum_seatsmax
           INTO TABLE @DATA(result_union).

    ASSERT result_union = result_grouping_sets.

    cl_demo_output=>display( result_grouping_sets ).

Description

For Lufthansa flights, this example calculates the sum of the maximum available seats with respect to the plane type (column planetype) and the connection (column connid). Two grouping sets are defined, which contain either the plane type or the connection.

The example shows that SELECT statements that use UNION ALL or GROUPING SETS are equivalent. The first query defines the grouping sets ( carrid, planetype ) and ( carrid, connid ) with a GROUP BY clause. The second query, on the other hand, specifies the same grouping sets using two GROUP BY clauses and merges them using UNION ALL.