Grouping Lines 

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

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 <a 1>] <s 2> [AS <a 2>] ...
               <agg> <sm> [AS <a m>] <agg> <s n> [AS <a n>] ...
       ...
       GROUP BY <s1> <s 2> ....

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. Alias names 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.

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 <s 1 > <s 2 > .....

Example

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 is as follows:

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.