ABAPGROUPBY_CLAUSE - GROUPBY CLAUSE

Addresses (Business Address Services)   RFUMSV00 - Advance Return for Tax on Sales/Purchases  
This documentation is copyright by SAP AG.

SELECT, GROUP BY

Short Reference

... GROUP BY ${ ${ sql_exp1, sql_exp2 ...
                 grouping_sets1, grouping_sets2, ...$}
             $| (grouping_syntax) $} ...

Additions

1. ... sql_exp1, sql_exp2, ...

2. ... grouping_sets1, grouping_sets2, ...

3. ... (grouping_syntax)

Effect

The addition GROUP BY combines groups of rows of the result set of a query into one row. After GROUP BY, the grouping criteria are specified statically or dynamically. The static specification is a comma-separated list of the following:

• SQL expressions sql_exp1, sql_exp2, ...

• Grouping sets grouping_sets1, grouping_sets2, ...

The order of the SQL expressions or grouping sets within the comma-separated list is not important. The dynamic specification is as a parenthesized data object grouping_syntax.

If used, GROUP BY demands that only individual elements col_spec and not all the columns are specified in the SELECT list using *. If GROUP BY is used, all columns that are specified directly after SELECT or as an argument of an SQL expression and not as the argument of an aggregate function, except the grouping function, must be specified in an aggregate expression. This means that columns not specified after GROUP BY can only be specified after SELECT as the argument of an aggregate function of an aggregate expression, with the exception of the grouping function. The aggregate expressions determine how the content of these columns is determined in the combined row from the content of all the rows of a group. For the grouping function GROUPING, the column that is specified as its argument must be listed after GROUP BY.

The SQL expressions specified after GROUP BY cannot have the type STRING, RAWSTRING, LCHR, LRAW, or GEOM_EWKB.

Notes

• If GROUP BY is used, the statement SELECT bypasses table buffering.

• Using GROUP BY and aggregate functions ensures that aggregates and groups are already built by the database system, not AS ABAP. This can considerably reduce the volume of data that has to be transported from the database to AS ABAP.
  
  

Addition 1

... sql_exp1, sql_exp2, ...

Effect

Specifies SQL expressions whose result is used for grouping. A group is formed by the rows that have the same result in all SQL expressions sql_exp1, sql_exp2, .... Every SQL expression specified after GROUP BY must also be specified somewhere in the SELECT list, with identical spelling. When specifying individual columns col, the same column names as in the SELECT list must be specified. It is not possible to specify alias names defined with AS.

When a column col is specified as the operand of an SQL expression after GROUP BY, the effect on the interaction with the SELECT list is the same as specifying the column individually. A column that is not the argument of an aggregate function in the SELECT list can be specified either individually after GROUP BY or as the operand of an SQL expression. If a column like this is specified as the operand of an SQL expression, it does not need to be specified individually.

All SQL expressions, except for aggregate expressions and window expressions, possible in the SELECT list can be specified after GROUP BY, with the following restrictions:

• Each expression must contain at least one column of a data source as its operand.

• The expressions cannot contain any host variables or ABAP literals as operands, with the exception of statically known constants or literals with the data type i or with a flat character-like type.

Notes

• If a grouping criterion specified after GROUP BY contains null values in the result set, these values are not part of the group of ABAP-specific initial values and form a distinct group instead.

• If individual columns col that are grouped in the SELECT list in an SQL expression are specified directly after GROUP BY, multiple groups with the same result can arise. To prevent this, the columns must be grouped after the expression.

• If specified columns contain a path expression for CDS associations or CTE associations, these must be the same in the GROUP BY clause and in the SELECT list, whereby parameter passing and attribute specifications are also compared.

• The spelling of the expressions after GROUP BY and in the SELECT list must be identical. An identical result is not enough. An expression col1 + col2 after GROUP BY does not have the same semantics as an expression col2 + col1 in the SELECT list. An alias name defined with AS in the SELECT list, however, is ignored.

• An SQL expression that is specified more than once outside of an aggregate expression in the SELECT list does not need to be specified more than once after GROUP BY. An SQL expression can be specified more than once after GROUP BY but has the same effect has specifying the expression once.

• After GROUP BY, it is not possible to specify, instead of an expression, the alias name defined in the rule with AS for the expression.

• Instead of using commas, only direct column specifications col1, col2, ... in an obsolete form, can be separated using blanks. Commas must be specified, however, in the strict modes of the syntax check from Release .

• If SQL expressions other than direct column specifications are specified after GROUP BY, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.

Example

The rows of the DDIC database table SFLIGHT that have the same content in column CARRID are combined. The lowest and highest values in column PRICE are determined for each of these groups and placed into the combined row.

Example

Grouping after a concatenation of the columns CARRID and CONNID.

SELECT, GROUP BY for SQL expressions

Addition 2

... grouping_sets1, grouping_sets2, ...

Effect

GROUPING SETS is an addition of the GROUP BY clause that can be used to define multiple grouping sets grouping_sets1, grouping_sets2, ... under a GROUP BY clause. The grouping sets are aggregated separately and grouped in a result set. For more information, see SELECT - GROUP BY, grouping_sets.

Addition 3

... (grouping_syntax)

Effect

As an alternative to specifying columns statically, a parenthesized data object grouping_syntax can be specified, which either contains the syntax of the list of SQL expressions (with the exception of host expressions) or is initial when the statement is executed. The same applies to grouping_syntax as when specifying columns dynamically as a SELECT list.

If the content of grouping_syntax is initial, either all the rows or no rows are grouped together. The columns in the SELECT list must then be specified either solely as arguments of aggregate functions or only directly. If not, the catchable exception CX_SY_OPEN_SQL_DB is raised. Invalid syntax raises a catchable exception from the class CX_SY_DYNAMIC_OSQL_ERROR.

See SQL Injections Using Dynamic Tokens.

Notes

• The conditions for specifying SQL expressions after GROUP BY are particularly relevant for dynamic tokens and a check at runtime verifies whether the expressions match those in the SELECT list:

• No host variables or ABAP literals can usually be specified in SQL expressions in grouping_syntax. In particular, static attributes or constants of a class cannot be accessed from outside if the class has a static constructor and the constructor was not yet executed.

• The class CL_ABAP_DYN_PRG contains methods that support the creation of correct and secure dynamically specified columns.
  
  

Example

After entering any column of DDIC database table SPFLI the selected data is organized according to this column, which means that similar entries are combined. In count the number of flight connections for the different values in column spflicol is determined. If, for example, CITYFROM is entered as spflicol, the number of destinations for each departure city is determined in count. Various possible exceptions are handled in TRY control structures. In particular, user input is checked for validity using a method of the class CL_ABAP_DYN_PRG.

RFUMSV00 - Advance Return for Tax on Sales/Purchases   General Material Data  
This documentation is copyright by SAP AG.

Length: 13652 Date: 20230201 Time: 104020     sap01-206 ( 224 ms )