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.
To specify the columns in the GROUP-BY clause statically, use:
SELECT lines s 1 [AS a 1 ] s 2 [AS a 2 ]... agg s m [AS a m ] agg s n [AS a n ]...... GROUP BY s 1 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. The alternative column names a 1 a 2 … 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.
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 ...
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:
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.