SAP NetWeaver AS ABAP Release 752, ©Copyright 2017 SAP AG. All rights reserved.
ABAP - Keyword Documentation → ABAP - Dictionary → ABAP CDS in ABAP Dictionary → ABAP CDS - Data Definitions → ABAP CDS - DDL for Data Definitions → ABAP CDS - DEFINE VIEW → ABAP CDS - SELECT → ABAP CDS - SELECT, Operands and Expressions →ABAP CDS - aggr_expr
Syntax
... { MAX( [ALL|DISTINCT] operand )
| MIN( [ALL|DISTINCT] operand )
| AVG( [ALL|DISTINCT] operand [AS dtype] )
| SUM( [ALL|DISTINCT] operand )
| COUNT( DISTINCT operand )
| COUNT(*) } ...
Effect
Aggregate expression in a SELECT statement of a CDS view. An aggregate expression calculates a single value from an operand operand by calling an aggregate function from multiple rows of a results set. The following table shows the possible aggregate functions:
Aggregate Function | Meaning | Result Type |
MAX | Greatest value of operand | Data type of operand |
MIN | Least value of operand | Data type of operand |
AVG | Average value of operand (the operand must be numeric). Type INT8 is only supported with addition AS dtype. The types DF16_..., DF34_... for decimal free floating numbers are not supported. | FLTP, platform-specific interim result or dtype |
SUM | Sum of operand (the operand must be numeric). The types DF16_..., DF34_... for decimal free floating numbers are not supported. | Data type of operand |
COUNT | If DISTINCT operand is specified, the number of distinct values of operand; if * is specified, the number of rows in the results set. | INT4 |
If ALL is used, all rows in the result set are taken into account (standard setting). If DISTINCT is used, only distinct values of operand are respected.
The following can be specified for operand:
The data type of the result is defined when the CDS view is activated and is determined by the aggregate function and the value range of the data types of the operands. If the result of aggregate function AVG is returned in a result set, the resul type is FLTP. As the interim result in a clause, the result type can be platform-specific. With addition AS dtype, the result type can be defined for every operand position.
Aggregate expressions can be used as elements of a SELECT list. Here they need an alternative element name defined using AS and require a GROUP BY clause to be used. Aggregate expressions can be compared with literals in a HAVING condition.
Notes
Example
In the SELECT list of the CDS view sales_order, aggregate expressions calculate the sum, the minimum, and the maximum of the gross amounts of each business partner. Also, the number of requests is determined. An alternative element name is defined for each aggregation. The non-aggregated fields buyer_guid and currency_code are specified in the GROUP BY clause.