ABAP Keyword Documentation → ABAP  Reference → Processing External Data → ABAP  Database Accesses → Open SQL → Open SQL  Read Accesses → SELECT → SELECT  result
SELECT  aggregate
Other versions: 7.31  7.40  7.54
Syntax
... { MAX( [DISTINCT] col )
 MIN( [DISTINCT] col )
 AVG( [DISTINCT] col )
 SUM( [DISTINCT] col )
 COUNT( DISTINCT col )
 COUNT( * )
 COUNT(*) } ... .
Effect
As many of the specified column labels as you like can be listed in the SELECT
command as arguments of the above aggregate expression. In aggregate expressions, a single value is
calculated from the values of multiple rows in a column as follows (note that the addition DISTINCT
excludes double values from the calculation):
MAX( [DISTINCT] col )
Determines the maximum value of the value in the column col
in the resulting set or in the current group. MIN( [DISTINCT] col )
Determines the minimum value of the content of the column col
in the resulting set or in the current group. AVG( [DISTINCT] col )
Determines the average value of the content of the
column col
in the resulting set or in the current group. The data type of the column has to be numerical. The data types DF16_RAW, DF16_SCL, DF34_RAW, and DF34_SCL are not allowed. SUM( [DISTINCT] col )
Determines the sum of the content of the column
col in the resulting set or in the current group. The data type of the column has to be numerical. The data types DF16_RAW, DF16_SCL, DF34_RAW, and DF34_SCL are not allowed. COUNT( DISTINCT col )
Determines the number of different values in the column col
in the resulting set or in the current group. COUNT( * )
(or COUNT(*)
) Determines the number of rows in the resulting set or in the current group. No column label is specified in this case.
If you are using aggregate expressions, all column labels that are not listed as an argument of an aggregate
function are listed after the addition GROUP BY
. The aggregate functions evaluate the content of the groups defined by
GROUP BY
in the database system and transfer the result to the combined rows of the resulting set.
The data type of aggregate expressions with the function MAX
, MIN
or SUM
is the data type of the corresponding column in the ABAP Dictionary. Aggregate expressions with the function AVG
for
decimal floating point numbers
have the corresponding data type (DF16_DEC or DF34_DEC); otherwise, they have the data type FLTP and
those with COUNT
have the data type INT4. The corresponding data object after
INTO
or APPENDING
has to be selected accordingly.
If the value of an aggregation expression is too large for the target area, an exception is raised. For the COUNT
function in particular, numbers larger than 2,147,483,647 are not permitted.
Note the following points when using aggregate expressions:

If the addition
FOR ALL ENTRIES
is used in front ofWHERE
, or if cluster or pool tables are listed afterFROM
, no other aggregate expressions apart from COUNT( * ) can be used. In such cases, the report of the aggregate expressions does not take place on the database, but instead is emulated on the application server.  Columns of the type STRING or RAWSTRING cannot be used with aggregate functions.

When aggregate expressions are used, the
SELECT
statement bypasses SAP buffering.  Null values are not included in the calculation for the aggregate functions. The result is a null value only if all the rows in the column in question contain the null value.

If only aggregate expressions are used after
SELECT
, the results set has one row and the additionGROUP BY
is not necessary. If a nontable type target area is specified afterINTO
, the commandENDSELECT
cannot be used together with the additionSINGLE
. If the aggregate expression COUNT( * ) is not being used, an internal table can be specified afterINTO
, and the first row of this table is filled. 
If aggregate functions are used without
GROUP BY
being specified at the same time, the resulting set also contains a row if no data is found in the database. If COUNT( * ) is used, the column in question contains the value 0. The columns in the other aggregate functions contain initial values. This row is assigned to the data object specified afterINTO
, and unless COUNT( * ) is being used exclusively,sysubrc
is set to 0 andsydbcnt
is set to 1. IfCOUNT( *)
is used exclusively, the additionINTO
can be omitted and if no data can be found in the database,sysubrc
is set to 4 andsydbcnt
is set to 0. 
In order to be able to evaluate the result of aggregate functions in
INTOAPPENDING CORRESPONDING FIELDS and
ORDER BY, an alternative column name must be specified using
AS
. This is then used by these additions.
Note
If the aggregate function SUM
for columns of the type DF16_DEC is used, we recommend using a target field of data type decfloat34
to avoid overflows.
Example
Determine the number of airlines flying to New York.
DATA count TYPE i.
SELECT COUNT( DISTINCT carrid )
FROM spfli
INTO count
WHERE cityto = 'NEW YORK'.
Example
Display the flight date, the number of passengers, the average and the maximum luggage weight of all Lufthansa flights with the flight number 0400.
DATA: fldate LIKE sbookfldate,
count TYPE i,
avg TYPE p DECIMALS 2,
max TYPE p DECIMALS 2.
SELECT fldate COUNT( * ) AVG( luggweight ) MAX( luggweight )
FROM sbook
INTO (fldate, count, avg, max)
WHERE carrid = 'LH' AND
connid = '0400'
GROUP BY fldate.
WRITE: / fldate, count, avg, max.
ENDSELECT.