SELECT - GROUP BY - ABAP Keyword Documentation

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

SELECT - GROUP BY

Quick Reference

Syntax

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

Extras:

1. ... col1, col2, ...

2. ... sql_exp1, sql_exp2, ...

3. ... grouping_sets1, grouping_sets2, ...

4. ... (grouping_syntax)

Effect

The addition GROUP BY combines groups of rows

that have the same content in the columns col1, col2, ...
and that have the same result in the SQL expressions sql_exp1, sql_exp2, ...,

as a single row in the results set of of a query. This can be specified either statically as a comma-separated list col1, col2 ..., sql_exp1, sql_exp2, ..., grouping_sets1, grouping_sets2, ... or dynamically as a parenthesized data object grouping_syntax. The order of the columns, SQL expressions or grouping sets within the comma-separated list is not important.

If used, GROUP BY demands that only individual elements but 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 must be specified here (except in the grouping function). This means that columns not specified after GROUP BY can only be specified after SELECT as the argument of an aggregate function (with the exception of the grouping function). The aggregate functions define how the content of these columns is determined in the combined row from the contents of all the rows of a group. For the grouping function, however, the column specified as the argument of the grouping function or specified directly in an SQL expression must be specified after GROUP BY or after GROUPING SETS.

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

Notes

If GROUP BY is used, the statement SELECT bypasses table buffering.
Using GROUP BY and aggregate functions ensures that aggregates and groups are assembled 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.
If GROUP BY is used together with SQL expressions after SELECT, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.

Addition 1

... col1, col2, ...

Effect

Specifies individual columns col1, col2, ..., directly whose content is used for grouping. The same column names must be specified as in the SELECT list. Alternative column names cannot be specified.

Notes

If a column specified after GROUP BY contains null values in the results set, these values are not part of the group of ABAP-specific initial values and create a distinct group instead.
If individual columns that are grouped in the SELECT in an SQL expression are specified after GROUP BY, multiple groups can arise with the same result. To prevent this, the columns must be grouped after the expression.
When a comma-separated list is used, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.
If specified, the columns col1, col2, ... can contain a path expressions for associations of a CDS view. These must match in the GROUP BY clause and in the SELECT list (parameter passings and specified attributes can be compared here too).
Instead of using commas, blanks can be used to separate the columns col1, col2, ... specified in an obsolete form. Commas must be specified, however, in the strict modes of the syntax check from Release 7.40, SP05.

Example

The rows of database table SFLIGHT that have the same contents 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.

SELECT FROM sflight
       FIELDS carrid,
              MIN( price ) AS min_price,
              MAX( price ) AS max_price
       GROUP BY carrid
       INTO TABLE @DATA(result).

cl_demo_output=>display( result ).

Addition 2

... sql_exp1, sql_exp2, ...

Effect

Specifies SQL expressions whose result is used for grouping. Every SQL expression specified after GROUP BY must also be specified somewhere in the SELECT list, with identical spelling.

When a column is used 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 or as the operand of an SQL expression after GROUP BY. 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 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 with any content, but not as an argument of the numeric functions or of the function COALESCE.

Data type c with length 1 with the content "X" or a blank, but not as an argument of the function COALESCE.

Notes

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 alternative column name specified 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 alternative column name specified in the SELECT list in the rule for the corresponding expression.
If SQL expressions 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

Creates a group using a chaining of the columns CARRID and CONNID.

SELECT FROM sflight
       FIELDS CONCAT( carrid, connid ) AS key,
              MIN( seatsocc ) AS min_seatsocc,
              MAX( seatsocc ) AS max_seatsocc
       GROUP BY CONCAT( carrid, connid )
       INTO TABLE @DATA(result).

cl_demo_output=>display( result ).

Executable Example

SQL Expressions, Use with GROUP BY

Addition 3

... grouping_sets1, grouping_sets2, ...

Effect

The GROUP BY addition GROUPING SETS makes it possible to group multiple aggregations under one SELECT statement. This can also be done by specifying the same SELECT statement with different GROUP BY clauses and using the addition UNION to group them in a single statement. This approach is more work, however, is error prone and is harder for the database to optimize. The addition GROUPING SETS also makes it easier to interpret and maintain the SELECT statement. More information can be found under SELECT - GROUP BY, grouping_sets.

Addition 4

... (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 columns or 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 at all 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, this would raise a handleable exception CX_SY_OPEN_SQL_DB. Invalid syntax raises a handleable exception from the class CX_SY_DYNAMIC_OSQL_ERROR.

Security Note

If used wrongly, dynamic programming techniques can present a serious security risk. Any dynamic content that is passed to a program from the outside must be checked thoroughly or escaped before being used in dynamic statements. This can be done using the system class CL_ABAP_DYN_PRG or the built-in function escape. 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 in cases where 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.

The literals of the dynamically specified ABAP SQL statements can span multiple rows of a token specified dynamically as an internal table.

When specified dynamically, ABAP SQL statements can contain the comment characters * and " as follows:

In a dynamic token specified as a character-like data object, all content is ignored from the first comment character ".

In a dynamic token specified as an internal table, all rows are ignored that start with the comment character *. In the row, all content is ignored from the first comment character ".

Comment characters placed within literals are, however, part of the literal.

Example

After entering any column of 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 tested for validity using a method of the class CL_ABAP_DYN_PRG.

DATA spflicol TYPE c LENGTH 20 VALUE 'CITYFROM'.
cl_demo_input=>request( CHANGING field = spflicol ).

TRY.
    spflicol =
      cl_abap_dyn_prg=>check_column_name( to_upper( spflicol ) ).
  CATCH cx_abap_invalid_name.
    cl_demo_output=>display( 'Not allowed' ).
    RETURN.
ENDTRY.

DATA dref TYPE REF TO data.
TRY.
    DATA(name) = `SPFLI-`  && spflicol.
    CREATE DATA dref TYPE (name).
    ASSIGN dref->* TO FIELD-SYMBOL(<fs>).
  CATCH cx_sy_create_data_error.
    cl_demo_output=>display( 'Not possible' ).
    RETURN.
ENDTRY.

DATA count TYPE i.
DATA(fieldlist) = spflicol && `, count(*)`.
TRY.
    SELECT DISTINCT (fieldlist)
           FROM spfli
           GROUP BY (spflicol)
           ORDER BY (spflicol)
           INTO (@<fs>, @count).
      cl_demo_output=>write( |{ <fs> } {
                                count }| ).
    ENDSELECT.
    cl_demo_output=>display( ).
  CATCH cx_sy_dynamic_osql_error.
    cl_demo_output=>display( 'Not possible' ).
    RETURN.
ENDTRY.

Continue
SELECT - GROUP BY, grouping_sets
agg_exp - GROUPING
Example

SELECT, Grouping Sets