1. ...
GROUP BY f1 ... fn
2. ... GROUP BY (source_text)
... GROUP BY f1 ... fn
Groups database table data in a SELECT
command on one line in the result set. A group is a set of lines which all have the same values in each
column determined by the
field descriptors
f1 ... fn.
...
GROUP BY f1 ... fn
always requires a list in the SELECT
clause. If you use field descriptors without an aggregate
funciton in the SELECTclause, you must list them in the GROUP BY f1 ... fn clause.
Output the number of passengers, the total weight and the average weight of luggage for all Lufthansa flights on 28.02.2001:
DATA: count TYPE I, sum TYPE P DECIMALS 2, avg TYPE F.
DATA: connid TYPE sbook-connid.
SELECT connid COUNT( * ) SUM( luggweight ) AVG( luggweight )
INTO (connid, count, sum, avg)
FROM sbook
WHERE
carrid = 'LH'
AND
fldate = '20010228'
GROUP BY connid.
WRITE: / connid, count, sum, avg.
ENDSELECT.
... GROUP BY f1 ... fn is not supported for pooled and
cluster tables.
The columns f1, ..., fn
must not be of the type STRING
or RAWSTRING.
... GROUP BY (source_text)
Works like
GROUP BY f1 ... fn if the variable source_text
contains the list f1 ... fn as ABAP source code.
The same restrictions apply to this variant as to GROUP BY f1 ... fn.
Output all Lufthansa departure points with the number of destinations:
DATA: BEGIN OF wa.
INCLUDE
STRUCTURE spfli.
DATA: count TYPE I.
DATA: END OF wa.
DATA:
field_list TYPE STRING,
group_by_list
TYPE STRING,
count TYPE I.
field_list =
'CITYFROM COUNT( * ) AS COUNT'.
group_by_list = 'CITYFROM'.
SELECT DISTINCT (field_list)
INTO CORRESPONDING FIELDS OF wa
FROM spfli
WHERE
carrid = 'LH'
GROUP BY (group_by_list).
WRITE: / wa-cityfrom, wa-count.
ENDSELECT.
Performance:
If aggregates and groups are formed by the database system and not just by the application server, this
helps to reduce considerably the volume of data that has to be transported from the database server to the application server.
Grouping Lines