Comments
ABAP GROUPBY CLAUSE Statement syntax, information and example SAP source code
SELECT - GROUP BY
Short Reference
ABAP Syntax
What does it do? The addition GROUP BY combines groups of rows that have the same content in their specified columns col1 col2 ... in the resulting set into a single row.
The use of GROUP BY has the prerequisite that SELECT only individual columns, not all the columns, are specified using * . If GROUP BY is used, all columns that are specified directly after SELECT and not specified as an argument of an
aggregate function must be listed there. This means that columns not listed after GROUP BY can only be listed 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.
After GROUP BY , the same column identifiers must be specified as after SELECT . This can either be specified statically as a list col1 col2 ... or dynamically as a parenthesized data object column_syntax that, when the statement is executed, contains the syntax of the static specification or is initial. For column_syntax , the same applies as to specifying columns dynamically after SELECT .
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 listed either solely as arguments of aggregate functions or solely directly. If not, this would trigger an exception
CX_SY_OPEN_SQL_DB that can be handled.
Columns specified after GROUP BY cannot be of the type
STRING , or RAWSTRING .
See SQL Injections Using Dynamic Tokens .
Latest notes: The addition GROUP BY cannot be specified for pool tables and cluster tables .
With the use of GROUP BY , the statement SELECT avoids the SAP buffering .
I 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.
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.
The class CL_ABAP_DYN_PRG contains methods that support the creation of correct and secure dynamic column specifications.
Example ABAP Coding 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
INTO (wa-carrid, wa-minimum, wa-maximum)
GROUP BY carrid.
cl_demo_output=>write_data( wa ).
ENDSELECT.
cl_demo_output=>display( ).
Example ABAP Coding 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 , then 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)
INTO (<(><<)>fs>, count)
FROM spfli
GROUP BY (spflicol).
cl_demo_output=>write( |{ <(><<)>fs> } {
count }| ).
ENDSELECT.
cl_demo_output=>display( ).
CATCH cx_sy_dynamic_osql_error.
MESSAGE 'Not allowed' TYPE 'E'.
ENDTRY.
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved
