Ansicht
Dokumentation

ABENCDS_F1_AGGREGATE_FUNCTIONS - CDS F1 AGGREGATE FUNCTIONS

ABENCDS_F1_AGGREGATE_FUNCTIONS - CDS F1 AGGREGATE FUNCTIONS

Fill RESBD Structure from EBP Component Structure   rdisp/max_wprun_time - Maximum work process run time  
This documentation is copyright by SAP AG.
SAP E-Book

- aggr_expr

... ${ 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 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 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. The types DATN, TIMN, and UTCL are not supported either. ,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. The types DATN, TIMN, and UTCL are not supported either. 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 result set. 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 operand are respected.

The following can be specified for operand:

  • A field of a data source data_source of the current CDS view
  • 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-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

  • The names of the aggregate functions AVG, COUNT, MAX, MIN, and SUM are protected and cannot be used as self-defined names.
  • The result of the aggregate functions AVG in data type FLTP is platform-dependent.

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.

@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





PERFORM Short Reference   SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up  
This documentation is copyright by SAP AG.

Length: 11241 Date: 20240329 Time: 093700     sap01-206 ( 111 ms )