Skip to content

ABAP Keyword Documentation →  ABAP Dictionary →  ABAP CDS in ABAP Dictionary →  ABAP CDS - Views →  ABAP CDS - DDL Statements →  ABAP CDS - DEFINE VIEW →  ABAP CDS - SELECT →  ABAP CDS - SELECT, Operands and Expressions 

ABAP CDS - aggregate

Other versions: 7.31 | 7.40 | 7.54

Syntax


... { MAX( [DISTINCT] operand ) 
    | MIN( [DISTINCT] operand ) 

    | AVG( [DISTINCT] operand ) 
    | SUM( [DISTINCT] operand ) 
    | COUNT( DISTINCT operand ) 
    | COUNT(*) } ...

Effect

Aggregate expression in a SELECT statement of a CDS view in ABAP CDS. 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
MAX Greatest value of operand
MIN Least value of operand
AVG Average value of operand
SUM Sum of operand
COUNT If DISTINCT operand is specified, the number of distinct values ofoperand, and if * is specified, the number of rows in the results set.

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

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.

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.

  • A CDS view with aggregate expressions in the SELECT list cannot be expanded using EXTEND VIEW.


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 and 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