Skip to content

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 

ABAP CDS - AVG, AS

Other versions: 7.31 | 7.40 | 7.54

Syntax


... AVG( [DISTINCT] operand AS dtype ) ...

Effect

Aggregate expression AVG of a CDS view with a data type dtype specified. The data type determines the result type in which the average value is returned.

The same can be specified for the operand as for all aggregate expressions with one exception; CASE cannot be specified. The data type of operand must be numeric and have one of the types INT1, INT2, INT4, INT8, DEC, CURR, QUAN, or FLTP.

The data type dtype can have the built-in numeric type DEC, CURR, QUAN, DECFLOAT16, DECFLOAT34, or FLTP. The data type FLTP, however, can only be specified for operands of type FLTP. The data type dtype can be specified as follows:

The following table shows the syntax for specifying built-in data types:

dtype Dictionary Type
abap.dec(len,decimals) DEC with length len and decimals decimal places
abap.curr(len,decimals) CURR with length len and decimals decimal places
abap.quan(len,decimals) QUAN with length len with decimals decimal places
abap.fltp[(16,16)] FLTP

The value range of the data type dtype must cover the value range of the operand type:

  • If operand is an integer type, a type dtype of type DEC, CURR, QUAN must have at least as many integer digits as is required by the value range of the respective integer type, that is, 3 for INT1, 5 for INT2, 10 for INT4, and 19 for INT8. There is no restriction on the number of decimal places.
  • If operand has type DEC, CURR, QUAN, a type dtype of type DEC, CURR, QUAN must have at least any many integer digits and decimal places.
  • If operand has type FLTP, this type must also be specified for dtype.

If the average value cannot be represented exactly in the data type dtype, the result is rounded commercially.


Notes

  • The addition AS to AVG bridges the gap for average values where FLTP cannot be cast to other numeric data types with CAST.

  • Since average values usually have decimal places, integer types are not supported for dtype. If necessary, the type DEC can be used without decimal places.

  • An aggregate expression AVG with the addition AS dtype can be used in accordance with this type in a CAST expression.

  • An aggregate expression AVG cannot be applied to operands with the data type INT8 without using the addition AS. The addition AS can also be used to form average values for columns with data type INT8.

  • The actual calculation of the average value (determined by the involved data types) is platform-dependent. This can produce underflows, overflows, and exceptions during the calculation. To prevent exceptions, the total of the values for which the average value is being calculated should be within the value range of dtype.

  • If FLTP is specified explicitly for operands of type FLTP, this ensures that the result of AVG actually has this type at each operand position, which cannot be guaranteed otherwise.


Example

The following CDS view demonstrates different uses of the aggregate expression AVG. The program DEMO_CDS_AVG accesses the view. The average value is calculated with and without addition DISTINCT and is returned in various types.

@AbapCatalog.sqlViewName: 'DEMOCDSAVG'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view Demo_Cds_Avg
  as select from
    demo_expressions
    {
      avg(          num1                   ) as avg_no_type,
      avg( distinct num1                   ) as avg_no_type_distinct,
      avg(          num1 as abap.dec(10,0) ) as avg_dec0,
      avg( distinct num1 as abap.dec(10,0) ) as avg_dec0_distinct,
      avg(          num1 as abap.dec(12,2) ) as avg_dec2,
      avg( distinct num1 as abap.dec(12,2) ) as avg_dec2_distinct
    }