SELECT, GROUP BY, grouping_sets - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Programming Language → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Read Access → SELECT, clauses → SELECT, GROUP BY →

Mail Feedback

SELECT, GROUP BY, grouping_sets

Short Reference

Syntax

... GROUPING SETS ( ( { }
                    | { sql_exp1, sql_exp2, ... } ),
                    ( { }
                    | { sql_exp1, sql_exp2, ... } ), ... ) ...

Effect

GROUPING SETS is an addition of the GROUP BY clause that allows the definition of multiple grouping sets under a GROUP BY clause. The grouping sets are aggregated separately and grouped in a result set.

The GROUP BY addition GROUPING SETS consists of a comma-separated list of grouping sets enclosed in parentheses. Each grouping set is itself parenthesized and is specified as follows:

As an empty grouping set ( )
An empty grouping set represents an aggregation across the entire data source. It is used, for example, to calculate a total sum.
As a comma-separated list ( sql_exp1, sql_exp2, ... )
A comma-separated list consisting of SQL expressions sql_exp1, sql_exp2, ... that defines the set of expressions to be aggregated.

Each grouping is viewed as a separate GROUP BY list and is evaluated as such. Here, the SQL expressions sql_exp1, sql_exp2, ... outside of the grouping set are also respected. The following two examples demonstrate this:

GROUP BY sql_exp1, GROUPING SETS( ( sql_exp2 ), ( sql_exp3, sql_exp4 ) )

GROUP BY GROUPING SETS( ( sql_exp1, sql_exp2 ), (sql_exp1, sql_exp3, sql_exp4 ) )

The results of the two GROUP BY clauses are equivalent and are the same as two SELECT statements joined using UNION ALL. The following two GROUP BY lists are used here:

sql_exp1, sql_exp2
sql_exp1, sql_exp3, sql_exp4

The addition GROUPING SETS has an advantage over a UNION clause grouping because the SELECT clause only needs to be specified once. It is also potentially easier for the database to optimize a statement with the addition GROUPING SETS than its UNION equivalent.

Rules

All columns used in the addition GROUPING SETS must be specified in the SELECT list.
The expressions specified in GROUPING SETS cannot have the data type LCHR, LRAW, RAWSTRING, STRING, or GEOM_EWKB.
The result rows, plus the SQL expressions, can be in any order in the comma-separated list and the order does not affect the result of the aggregation. If the results of the aggregation need to be sorted in a specific way, an ORDER BY clause must be specified. The ORDER BY addition PRIMARY KEY is allowed.
The expressions that are part of the aggregation contain the null values as placeholders in the corresponding results.

Tips

The grouping function GROUPING can be used to check whether a specific column in the result set was aggregated or not.

Hints

The GROUP BY addition GROUPING SETS cannot be processed by the ABAP SQL engine. The ABAP SQL statement bypasses the table buffer and an internal table accessed by FROM @itab must be transported to the database. This is only possible for one internal table per ABAP SQL statement.
If the addition GROUPING SETS is used, the syntax check is performed in a strict mode, which handles the SELECT statement more strictly than the regular syntax check.
Grouping sets are not supported by all databases. In an ABAP program, it is possible to use the method USE_FEATURES of the class CL_ABAP_DBFEATURES to check whether the current database system or a database system accessed using a secondary connection supports access to grouping sets. This requires the constant GROUPING_SETS of the class to be passed to the method in an internal table.

Example

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

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 @FINAL(result_grouping_sets).

cl_demo_output=>display( result_grouping_sets ).

Executable Example

See SELECT, Grouping Sets