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, clauses
ABAP CDS - SELECT, GROUP BY
Other versions:
7.31 | 7.40 | 7.54
Syntax
... GROUP BY field1, field2, ...
Effect
Combines the rows of the result set of a CDS view in ABAP CDS (which have the same content in the specified fields field1, field2, ...) into one row. The fields must be specified using the same names as the fields in the data source data_source. The current CDS view cannot be specified using alternative element names, which are defined with AS.
Any elements of the CDS view that which are not listed after GROUP BY must be defined in the SELECT list using aggregate expressions. Conversely, GROUP BY must be used, if aggregate expressions are contained in the SELECT list, and all elements not defined in using aggregate expressions must be listed after GROUP BY. Literals and other expressions cannot be listed after GROUP BY. If expressions or calls of predefined functions are listed with aggregate expressions as elements of the SELECT list, then all the fields used must be listed in the GROUP BY clause.
When the CDS view is accessed, the results of the aggregate expressions are calculated from the values of the corresponding fields of the combined rows and the results are placed in the element of the resulting row in the result set.
Note
A WHERE condition is evaluated before the rows are combined using GROUP BY.
Example
When a CDS view is accessed, the view returns sales_order for every role of a business partner, and returns the number of business partners and the total of all gross amounts for every currency.
define view sales_order as
select from snwd_so
inner join
snwd_bpa on buyer_guid = snwd_bpa.node_key
{ bp_role as role, //e.g. customer or supplier
count(distinct buyer_guid) as partners_count,
@Semantics.currencyCode snwd_so.currency_code,
@Semantics.amount.currencyCode: 'currency_code'
sum(snwd_so.gross_amount) as sum_gross_amount }
group by bp_role, snwd_so.currency_code;