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, clauses
ABAP CDS - SELECT, GROUP BY
Other versions:
7.31 | 7.40 | 7.54
Syntax
... GROUP BY field1, field2, ...
path_expr1, path_expr2, ...
Effect
Groups those rows in the results set of a CDS view in that have the same content in the elements specified by the fields field1, field2, ... or path expressions path_expr1, path_expr2 ... as a single 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 are not specified 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 using aggregate expressions must be specified after GROUP BY. Literals and other expressions cannot be specified after GROUP BY. If expressions or calls of built-in functions are specified as elements of the SELECT list using aggregate expressions, all the fields used must be specified 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 results set.
The fields specified after GROUP BY cannot be of the type LCHR, LRAW, STRING, RAWSTRING, or GEOM_EWKB.
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;