Entering content frame

Grouping Lines Locate the document in its SAP Library structure

The GROUP-BY clause summarizes several lines from the database table into a single line of the selection.

This graphic is explained in the accompanying text

The GROUP-BY clause allows you to summarize lines that have the same content in particular columns. Aggregate functions are applied to the other columns. You can specify the columns in the GROUP-BY clause either statically or dynamically.

Specifying Columns Statically

To specify the columns in the GROUP-BY clause statically, use:

SELECT lines s1 [AS a1] s2 [AS a2]...
                agg sm [AS am] agg sn [AS an]...
...
  GROUP BY s1 s2....

To use the GROUP-BY clause, you must specify all of the relevant columns in the SELECT clause. In the GROUP-BY clause, you list the field names of the columns whose contents must be the same. You can only use the field names as they appear in the database table. The alternative column names a1 a2… from the SELECT clause are not allowed.

All columns of the SELECT clause that are not listed in the GROUP-BY clause must be included in aggregate functions. This defines how the contents of these columns is calculated when the lines are summarized.

This graphic is explained in the accompanying text

Specifying Columns Dynamically

To specify the columns in the GROUP-BY clause dynamically, use:

... GROUP BY (itab)...

where itab is an internal table with line type c and maximum length 72 characters containing the column names <s1 s2 ...

Example

REPORT demo_select_group_by.

DATA: carrid TYPE sflight-carrid,
      minimum TYPE p DECIMALS 2,
      maximum TYPE p DECIMALS 2.

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

  WRITE: / carrid, minimum, maximum.

ENDSELECT.

The output looks something like this:

This graphic is explained in the accompanying text

The lines in the database table SFLIGHT that have the same value in the column CARRID are summarized. The smallest and largest values of PRICE are determined for each group and placed in the summarized line.

 

 

 

Leaving content frame