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

SELECT - GROUP BY

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... GROUP BY { { sql_exp1, sql_exp2 ... 

                
grouping_sets1, grouping_sets2, ...} 

             | (grouping_syntax) } ...

Extras

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

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

3. ... (grouping_syntax)

Effect

The addition GROUP BY collates groups of rows of the result set of a query in 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, 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 (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 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 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 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.

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, and so on. 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 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, 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, they create 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 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 specified 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, and so on in an obsolete form, can be separated using blanks. Commas must be specified, however, in the strict modes of the syntax check from Release 7.40, SP05.
If other SQL expressions are specified as direct column specifications 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 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 ).

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

SELECT, GROUP BY for SQL expressions

Addition 2

... grouping_sets1, grouping_sets2, ...

Effect

GROUPING SETS is an addition of the GROUP BY clause 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 results set. More information can be found under 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 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 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 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

sql_agg - GROUPING

SELECT, GROUP BY for SQL Expressions

SELECT, Grouping Sets

GROUPING, Grouping Function