SAP CDS AGGREGATE FUNCTIONS V1

Get Example source ABAP code based on a different SAP table
  SAP Help

• MAX ABAP_CDS_EXPR_V1
• MIN ABAP_CDS_EXPR_V1
• AVG ABAP_CDS_EXPR_V1
• SUM ABAP_CDS_EXPR_V1
• COUNT ABAP_CDS_EXPR_V1

ABAP_CDS_DDL - DDIC-Based View, aggr_expr

ABAP_SYNTAX
... ${ MAX( $[ALL$|DISTINCT$] arg )
$| MIN( $[ALL$|DISTINCT$] arg )
$| AVG( $[ALL$|DISTINCT$] arg $[AS dtype$] )
$| SUM( $[ALL$|DISTINCT$] arg )
$| COUNT( DISTINCT arg )
$| COUNT(*) $} ...

What does it do?
Aggregate expression in a SELECT statement of a ABAP_CDS_V1_VIEW . An aggregate expression calculates a single value from an operand arg by calling an aggregate function from multiple rows of a result set. The operands can have any data type except LCHR, LRAW, STRING, RAWSTRING, or GEOM_EWKB . The following table shows the possible aggregate functions: Aggregate FunctionMeaningResult Type
MAXGreatest value of argData type of arg
MINLeast value of argData type of arg
AVGAverage value of arg (arg must be numeric). Type INT8 is only supported with addition AS dtype. The types DF16_..., DF34_... for decimal free floating numbers are not supported. The types DATN, TIMN, and UTCL are not supported either., FLTP, platform-dependent interim result or dtype
SUMSum of arg (arg must be numeric). The types DF16_..., DF34_... for decimal free floating numbers are not supported. The types DATN, TIMN, and UTCL are not supported either. Data type of arg
COUNTIf DISTINCT arg is specified, the number of distinct values of arg is counted; if * is specified, the number of rows in the result set is counted. INT4
If ALL is used, all rows in the result set are respected, which is the standard setting. If DISTINCT is used, only distinct values of arg are respected.
The following can be specified for arg:

A literal

A field of a data source data_source of the current CDS view

A path expression that identifies a field of a data source data_source

A case distinction CASE that returns a value
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 result type is FLTP. As the interim result in a clause, the result type can be platform-dependent. 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 .



Latest notes:

The aggregate expression SUM is calculated on the database. The database platform determines whether an overflow occurs if the result of an aggregate expression exceeds its value range. See also:

SAP HANA SQL Reference Guide, SUM Function (Aggregate)

SAP HANA SQL Reference Guide, Numerical Effects

The names of the aggregate functions AVG, COUNT, MAX , MIN, and SUM are protected and cannot be used as user-defined names.

To add aggregate expressions in the SELECT list to a CDS view using EXTEND VIEW, the values #PROJECTION_LIST and #GROUP_BY must be specified with the annotation AbapCatalog.viewEnhancementCategory[ ].

The result of the aggregate functions AVG in data type FLTP is platform-dependent.

ABAP_CAUTION When using count(*) on a client-dependent table which has no entries, then no result is returned. The expected behavior would be that a '0' is returned, but instead, the result field remains empty (it also does not contain the null value). count(*) on an empty, client-independent table returns '0', as expected.
NON_V5_HINTS
ABAP_HINT_END

ABAP_EXAMPLE_VX5
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. @AbapCatalog.sqlViewName: 'SALES_ORDER_VW'
define view sales_order as
select from snwd_so
{ key buyer_guid,
@Semantics.currencyCode
currency_code,
@Semantics.amount.currencyCode: 'currency_code'
sum(gross_amount) as sum_gross_amount,
@Semantics.amount.currencyCode: 'currency_code'
min(gross_amount) as min_gross_amount,
@Semantics.amount.currencyCode: 'currency_code'
max(gross_amount) as max_gross_amount,
@Semantics.amount.currencyCode: 'currency_code'
avg(gross_amount) as avg_gross_amount,
count(*) as sales_orders_count }
group by buyer_guid, currency_code
ABAP_EXAMPLE_END