Aggregate Expressions in ABAP CDS Views

2
9622

Hello everyone,

In this ABAP CDS Views tutorials you will learn how to use Aggregate Expressions in ABAP CDS Views.

To access all ABAP CDS Views tutorials click here. Lets get started.

Prerequisites

  • You have installed Eclipse IDE( Mars Version )on your local machine.Click here to know more about.
  • You have installed ABAP Development Tools in Eclipse IDE.
  • You have access to minimum ABAP Netweaver 7.4 on HANA.
  • You have created ABAP Project in eclipse to connect to ABAP Netweaver 7.4 system.Click here to know how to create ABAP Project.

Below is the list of aggregation expressions used in ABAP CDS views.

1SUM

@AbapCatalog.sqlViewName: 'ZCDS_AGGR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Aggregations'

define view Z_Cds_Agg_sum as select from snwd_stock 
    join snwd_pd on snwd_stock.product_guid = snwd_pd.node_key
    {    
    key snwd_pd.product_id,
    snwd_pd.category,    
    // Aggregate function "SUM"
    sum(snwd_stock.quantity) as total_stock    
    } 
    group by snwd_pd.category,
             snwd_pd.product_id

In the above example ABAP CDS view selects the total stock of the product by using the aggregate function SUM and GROUP BY product and category.

2MAX

@AbapCatalog.sqlViewName: 'ZCDS_AGGR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Aggregations'
define view Z_Cds_Agg_max as select from snwd_so
          join snwd_bpa on snwd_so.buyer_guid = snwd_bpa.node_key
          {
           key  snwd_bpa.bp_id,
                snwd_bpa.company_name,
           // Aggregate function "MAX"
           max(snwd_so.gross_amount) as max_sales_amt
} group by snwd_bpa.bp_id,
           snwd_bpa.company_name

In the above example ABAP CDS view selects the maximum sales amount generated by the customer by using the aggregate function MAX and GROUP BY business partner.

3MIN

@AbapCatalog.sqlViewName: 'ZCDS_AGGR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Aggregations'
define view Z_Cds_Agg_min as select from snwd_so
          join snwd_bpa on snwd_so.buyer_guid = snwd_bpa.node_key
          {
           key  snwd_bpa.bp_id,
                snwd_bpa.company_name,
           // Aggregate function "MIN"
           min(snwd_so.gross_amount) as min_sales_amt
} group by snwd_bpa.bp_id,
           snwd_bpa.company_name

In the above example ABAP CDS view selects the minimum sales amount generated by the customer by using the aggregate function MIN and GROUP BY business partner.

4COUNT( * )

@AbapCatalog.sqlViewName: 'ZCDS_AGGR2'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Aggregations'
define view Z_Cds_Agg as select from snwd_so
          join snwd_bpa on snwd_so.buyer_guid = snwd_bpa.node_key
          {
           key  snwd_bpa.bp_id,
                snwd_bpa.company_name,
           // Aggregate expression COUNT( * )
           count(*) as min_sales_amt
} group by snwd_bpa.bp_id,
           snwd_bpa.company_name

In the above example ABAP CDS view selects the total number of sales order created against the business partner by using the aggregate function COUNT( * ) and GROUP BY business partner.

5COUNT( DISTINCT )

@AbapCatalog.sqlViewName: 'ZCDS_AGGR_4'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Aggregate Expressions'
define view Zcds_Agg_C as select from snwd_so_i         
       join snwd_pd on snwd_so_i.product_guid = snwd_pd.node_key        
     {
        key snwd_pd.product_id,                    
        // Aggregate Expression - COUNT( DISTINCT )
        count( distinct snwd_so_i.node_key) as orders_count                
} group by snwd_pd.product_id

In the above example ABAP CDS view selects the total number of sales order created against the product by using the aggregate function COUNT( DISTINCT ) and GROUP BY product id.

Points to be remembered

  1. Every aggregate expressions used need an alternative element name defined using AS.
  2. Aggregate expressions should require GROUP BY clause.
  3. All non-aggregated fields used in CDS view should be specified in the GROUP BY clause.

Congrats! You have successfully learned different Aggregate Expressions in ABAP CDS Views.

Please stay tuned for ABAP CDS View tutorials. Leave a comment in the below comment section and let us know your feedback.