SAP CDS AGGREGATE FUNCTIONS V2 Get Example source ABAP code based on a different SAP tableSAP Help
• MAX ABAP_CDS_EXPRESSION
• MIN ABAP_CDS_EXPRESSION
• AVG ABAP_CDS_EXPRESSION
• SUM ABAP_CDS_EXPRESSION
• COUNT ABAP_CDS_EXPRESSION
ABAP_CDS_DDL - CDS View Entity, aggr_expr ABAP_SYNTAX ... ${ MAX( $[ALL$|DISTINCT$] arg ) AS dtype ) What does it do? SELECT statement of a CDS view entity. An aggregate expression calculates a single value from multiple rows of the operand arg. The operands can have any data type except for LCHR, LRAW, STRING, RAWSTRING, or GEOM_EWKB . The following table shows the possible aggregate functions: Aggregate FunctionMeaningResult Type MAXReturns the greatest value of argData type of arg MINReturns the least value of argData type of arg AVGAverage value of arg. Must be specified with the addition AS dtype. The following data types are supported: INT1, INT2, INT4, INT8, FLTP, DEC, CURR, QUAN, D16N, and D34N. Must be specified explicitly after AS. Possible data types are DEC, CURR, QUAN, D16N ,D34N, and FLTP. SUMSum of arg. The following data types are supported: INT1, INT2, INT4, INT8, FLTP, DEC, CURR, QUAN, D16N, and D34N. 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 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. If an argument of an aggregate function has the null value, it is ignored when the function is evaluated. This also applies to COUNT with the addition DISTINCT: null values are not considered as a distinct value, and are not counted. The result of an aggregate function is a null value only if all the rows in the column in question contain a null value. The aggregate function COUNT counts rows and never produces the null value. arg: A literal A field of a data source data_source of the current CDS view entity A parameter A session variable A path expression that identifies a field of a data source data_source A case distinction CASE that returns a value A cast expression Any built-in function that returns a suitable data type An arithmetic expression A reuse expression using $projection AVG must always be specified with the addition AS dtype. Amounts and Quantities in Aggregate Expressions arg of an aggregate function is a CDS amount field, a CDS quantity field, or a CDS calculated quantity, the result type might require a reference annotation as well. The following table shows the result type depending on the operand type of all available aggregate functions. Aggregate FunctionType of OperandResult Type MAX, MIN, SUM, AVGamount amount MAX, MIN, SUM, AVGquantity quantity MAX, MIN, SUM, AVGcalculated quantity calculated quantity COUNTamount, quantity, calculated quantity number of type INT4 Aggregate expressions can be used as elements of a SELECT list. There they need an alternative element name defined using AS and require a GROUP BY clause. The GROUP BY clause must list all non-aggregated fields from the SELECT list. Aggregate expressions can be compared with literals in a HAVING condition . Aggregate expressions can be used as operands in built-in functions, cast expressions, or case distinctions. An aggregate expression can be used in the condition cds_cond of a complex case distinction as the operand lhs or as the operand rhs if a comparison operator is used as relational operator. Latest notes: The aggregate expression SUM is calculated on the database. The database platform determines whether an overflow occurs if the result of SUM 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. It is possible to add aggregate expressions to the SELECT list of a CDS view entity by means of a CDS view entity extension (using EXTEND VIEW ENTITY ). The following conditions must be met: The extended entity must explicitly allow aggregate expressions and the annotation AbapCatalog.viewEnhancementCategory[ ] must be set to #PROJECTION_LIST and #GROUP_BY. ABAP_EXAMPLE_VX5 AVG, SUM, MIN, MAX, and COUNT(*). An alternative element name is defined for each aggregation. The class CL_DEMO_CDS_AGGREGATE_VE fills the underlying database table, reads the view entity and returns the result. ABAP_EXAMPLE_VX5 COUNT. Field count1 returns the result 1, field countNull returns 0. This demonstrates that COUNT with the addition DISTINCT ignores null values.
