ABAPGROUPBY_CLAUSE - GROUPBY CLAUSE

CL_GUI_FRONTEND_SERVICES - Frontend Services   PERFORM Short Reference  
This documentation is copyright by SAP AG.

SELECT - group

Short Reference

Syntax

... GROUP BY { {col1 col2 ...} | (column_syntax) } ... .

Effect

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 SELECTonly 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 aggregatefunction must be listed there. Conversely, if GROUP BY is used, all the columns listed after SELECT that are not specified after GROUP BY must be specified as an argument of anaggregatefunction. 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 columnidentifiers must be specified as after SELECT. The specification can either be specifiedstatically as a list col1 col2 ... or dynamically as a bracketed data object column_syntaxthat - at execution of the statement - contains the syntax of the static specification or is set to initial value. For column_syntax, the same applies as for the dynamic column specification 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.

The columns listed after GROUP BY must not be of type STRING or RAWSTRING, and if GROUP BY is used,pool orcluster tables cannot be accessed.

Note

With the use of GROUP BY, the statement SELECT avoids the SAP buffering.

Example

The rows of database table SFLIGHT that havethe 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: carrid  TYPE sflight-carrid,
      minimum TYPE p DECIMALS 2,
      maximum TYPE p DECIMALS 2.

SELECT carrid MIN( price ) MAX( price )
       FROM sflight
       INTO (carrid, minimum, maximum)
       GROUP BY carrid.
  WRITE: / carrid, minimum, maximum.
ENDSELECT.

Example

After any column of database table SPFLI isentered, the selected data is organized according to this column, which means that similar entries arecombined. In count, the number of flight connections for the different values in column spflicolis determined. If you enter, for example, "cityfrom" as spflicol, then the number of destinations for each departure city is determined in count.

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.

long_name = 'spfli-' && spflicol.
CREATE DATA dref TYPE (long_name).
ASSIGN dref->* TO <fs>.

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

SELECT DISTINCT (fieldlist)
       INTO (<fs>, count)
       FROM spfli
       GROUP BY (spflicol).
  WRITE: / <fs>, count.
ENDSELECT.

Vendor Master (General Section)   SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up  
This documentation is copyright by SAP AG.


Length: 5049 Date: 20120522 Time: 064115     triton ( 208 ms )