Skip to content

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

SELECT - GROUP BY, grouping_sets

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


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

                    ( { } 

                 
  | { sql_exp1, sql_exp2, ... } ), ... ) ...

Effect

GROUPING SETS is an addition of the GROUP BY clause used to define multiple grouping sets under a GROUP BY clause. The grouping sets are aggregated separately and grouped in a results set.

The GROUP BY addition GROUPING SETS consists of a comma-separated list of grouping sets encloses 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 aggregate.

Each grouping is viewed as a separate GROUP BY list and is evaluated as a list of this type. 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 verify whether a specific column in the results set was aggregated or not.


    Notes

    • If the GROUP BY addition GROUPING SETS is used, the statement SELECT bypasses table buffering.
    • 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 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.

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

 cl_demo_output=>display( result_grouping_sets ).

    Executable Example

    See SELECT, Grouping Sets