SELECT - GROUP BY - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Reads → SELECT clauses →

SELECT - GROUP BY

Quick Reference

Syntax

... GROUP BY { { col1, col2, ...
sql_exp1, sql_exp2 ... }
| (column_syntax) } ...

Extras:

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

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

3. ... (column_syntax)

Effect

In the results set of a query, the addition GROUP BY combines groups of rows that have the same content in the columns col1, col2, ... or that have the same result in the SQL expressions sql_exp1, sql_exp2, ... into a single row. This can be specified either statically as a comma-separated list col1, col2 ..., sql_exp1, sql_exp2, ... or dynamically as a parenthesized data object column_syntax. The order of the columns or SQL expression 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. This means that columns not specified after GROUP BY can only be specified after SELECT as the argument of an aggregate 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.

The addition GROUP BY cannot be specified for pooled tables and cluster tables. Columns specified after GROUP BY cannot be of the type STRING, RAWSTRING, LCHR or LRAW.

Notes

If GROUP BY is used, the statement SELECT avoids the SAP buffering.
Using GROUP BY and aggregate functions ensures that aggregates and groups are assembled by the database system, not the application server. This can considerably reduce the volume of data that has to be transported from the database to the application server.
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.
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.

DATA:
  BEGIN OF wa,
    carrid  TYPE sflight-carrid,
    minimum TYPE p DECIMALS 2,
    maximum TYPE p DECIMALS 2,
  END OF wa.

SELECT carrid, MIN( price ), MAX( price )
       FROM sflight
       GROUP BY carrid
       INTO (@wa-carrid, @wa-minimum, @wa-maximum).
  cl_demo_output=>write_data( wa ).
ENDSELECT.
cl_demo_output=>display( ).

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.

PARAMETERS spflicol TYPE c LENGTH 20.

DATA: dref TYPE REF TO data,
      long_name TYPE string,
      count  TYPE i,
      fieldlist TYPE string.

FIELD-SYMBOLS <fs> TYPE any.

AT SELECTION-SCREEN.
  TRY.
      cl_abap_dyn_prg=>check_column_name( spflicol ).
    CATCH cx_abap_invalid_name.
      MESSAGE 'Not allowed' TYPE 'E'.
  ENDTRY.

START-OF-SELECTION.
  TRY.
      long_name = 'spfli-' && spflicol.
      CREATE DATA dref TYPE (long_name).
      ASSIGN dref->* TO <fs>.
    CATCH cx_sy_create_data_error.
      MESSAGE 'Not allowed' TYPE 'E'.
  ENDTRY.

  fieldlist = spflicol && ', count(*)'.

  TRY.
      SELECT DISTINCT (fieldlist)
             FROM spfli
             GROUP BY (spflicol)
             INTO (@<fs>, @count).
        cl_demo_output=>write( |{ <fs> } {
                                  count }| ).
      ENDSELECT.
      cl_demo_output=>display( ).
    CATCH cx_sy_dynamic_osql_error.
      MESSAGE 'Not allowed' TYPE 'E'.
  ENDTRY.

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 and 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.
The alternative column name usually specified in the SELECT list for an expression, cannot be specified after GROUP BY instead of the expression in question.
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.

Executable Example

SQL Expressions, Use with GROUP BY

Addition 3

... (column_syntax)

Effect

As an alternative to specifying columns statically, a parenthesized data object column_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 column_syntax as when specifying columns dynamically as a SELECT list.

If the content of column_syntax initial, either all the rows or no rows at all are grouped together. The columns after SELECT 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.

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 predefined 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 column_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 dynamically specified columns when created correctly and securely.

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

When specified dynamically, Open 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.