SAP GROUPING SETS CLAUSE ABAP Statements
Get Example source ABAP code based on a different SAP table
• GROUPING SETS SELECT
SELECT, GROUP BY, grouping_sets Short Reference
ABAP_SYNTAX
... GROUPING SETS ( ( ${ $}
$| ${ sql_exp1, sql_exp2, ... $} ),
( ${ $}
$| ${ sql_exp1, sql_exp2, ... $} ), ... ) ...
What does it do?
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.
Latest notes:
If the GROUP BY addition GROUPING SETS is used, the statement SELECT bypasses table buffering.
NON_V5_HINTS
If the addition GROUPING SETS is used, the syntax check is performed in a ABAP_STRICT_773 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.
ABAP_HINT_END
ABAP_EXAMPLE_VX5
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.
ABEXA 00313
ABAP_EXAMPLE_END
ABAP_EXAMPLE_ABEXA
See SELECT, Grouping Sets
ABAP_EXAMPLE_END
Return to menu
