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


Quick Reference


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


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

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

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

4. ... (grouping_syntax)


The addition GROUP BY combines groups of rows

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.


Addition 1

... col1, col2, ...


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.



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.

       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, ...


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:



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

       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, ...


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)


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.


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


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.

cl_demo_input=>request( CHANGING field = spflicol ).

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

DATA dref TYPE REF TO data.
    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' ).

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

SELECT - GROUP BY, grouping_sets
agg_exp - GROUPING
Example SELECT, Grouping Sets