首页 >
SAP >
HANA > Aggregate Expressions in ABAP CDS Views
2019
08-27
Aggregate Expressions in ABAP CDS Views
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
@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.
1.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_sale-ale settingss_amt } group by snwd_bpa.bp_id, snwd_bpa.company_name |
In the above example ABAP CDS view selects the maximum sale-ale settingss amount generated by the customer by using the aggregate function MAX and GROUP BY business partner.
2.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_sale-ale settingss_amt } group by snwd_bpa.bp_id, snwd_bpa.company_name |
In the above example ABAP CDS view selects the minimum sale-ale settingss amount generated by the customer by using the aggregate function MIN and GROUP BY business partner.
3.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_sale-ale settingss_amt } group by snwd_bpa.bp_id, snwd_bpa.company_name |
In the above example ABAP CDS view selects the total number of sale-ale settingss order created against the business partner by using the aggregate function COUNT( * ) and GROUP BY business partner.
4.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 sale-ale settingss order created against the product by using the aggregate function COUNT( DISTINCT ) and GROUP BY product id.
4.1.1.Points to be remembered
Every aggregate expressions used need an alternative element name defined using AS.
Aggregate expressions should require GROUP BY clause.
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.
