Comments
ABAP SELECT AGGREGATE Statement syntax, information and example SAP source code
SELECT - aggregate
Short Reference
ABAP Syntax
| MIN( [DISTINCT] col )
| AVG( [DISTINCT] col )
| SUM( [DISTINCT] col )
| COUNT( DISTINCT col )
| COUNT( * )
| COUNT(*) } ... .
What does it do? Any number of the specified column identifiers can be included in the SELECT statement as arguments of the aggregate expressions above. In aggregate expressions, a single value is calculated in multiple rows from the values of one column as follows (note that the addition DISTINCT excludes double values in the calculation):
MAX( [DISTINCT] col ) determines the maximum value of the value of column col in the results set or in the current group.
MIN( [DISTINCT] col ) determines the maximum value of the contents of column col in the results set or in the current group.
AVG( [DISTINCT] col ) determines the average value of the contents of column col in the results set or in the current group. The data type of the column must be numeric. The data types
DF16_RAW , DF16_SCL , DF34_RAW , and DF34_SCL are not permitted.
SUM( [DISTINCT] col ) determines the sum of the contents of column col in the results set or in the current group. The data type of the column must be numeric. The data types DF16_RAW ,
DF16_SCL , DF34_RAW , and DF34_SCL are not permitted.
COUNT( DISTINCT col ) determines the number of different values in column col in the restuls set or in the current group.
COUNT( * ) (or COUNT(*) ) determines the number of rows in the results set or in the current group. No column identifier is specified in this case.
If aggregate expressions are used, any column identifiers that are not included as arguments of an aggregate function must be included after the GROUP BY addition. The aggregate functions evaluate the contents of the groups defined by GROUP BY
in the database system and pass the result to the merged rows of the results set.
The data type of aggregate expressions with the function MAX , MIN , or SUM is the data type of the corresponding column in 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 . Aggregate expressions with the function 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 in relation to using aggregate expressions:
If the addition FOR ALL ENTRIES is used before
WHERE , or if cluster or pool tables are included after FROM
, no aggregate expressions apart from COUNT( * ) can be used. In cases like these, the aggregate expression is not evaluated in the database, but is emulated on the application server.
Columns of type STRING or RAWSTRING cannot be handled 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 a null value.
If aggregate expressions only are used after SELECT , the results set has just one row and the GROUP BY addition is not required. If a non-table-like target area is specified after INTO
the ENDSELECT statement cannot be specified as is the case when the SINGLE addition is used. If the aggregate expression
COUNT( * ) is not used, an internal table can be specified after
INTO , and the first row of this table is filled.
If aggregate functions only are used without GROUP BY , the results 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 for the other aggregate functions contain initial values. This row is assigned to the data object specified after INTO and, unless only COUNT( * ) is used, sy-subrc is set to 0 and sy-dbcnt is set to 1. If only COUNT( * ) is used, the addition INTO can be omitted and, if no data is found on the database, sy-subrc is set to 4 and sy-dbcnt is set to 0.
To be able to evaluate the result of aggregate functions in INTO|APPENDING CORRESPONDING FIELDS and ORDER BY , an alternative column name must be specified using AS . This is then used by these additions.
Latest notes: If the aggregate function SUM is used for columns of type DF16_DEC , we recommend using a target field of data type decfloat34 to avoid overflows.
Example ABAP Coding Determines 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 ABAP Coding Returns the flight date, the number of passengers, and the average and maximum luggage weight of all Lufthansa flights with the flight number 0400.
TYPES: BEGIN OF wa,
fldate LIKE sbook-fldate,
count TYPE i,
avg TYPE p DECIMALS 2,
max TYPE p DECIMALS 2,
END OF wa.
DATA itab TYPE TABLE OF wa WITH EMPTY KEY.
SELECT fldate COUNT( * ) AVG( luggweight ) MAX( luggweight )
FROM sbook
INTO TABLE itab
WHERE carrid = 'LH' AND
connid = '0400'
GROUP BY fldate.
cl_demo_output=>display( itab ).
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved
