ABAP Cloud Course

Appearance

Lesson 7 Companion — CDS Aggregation, GROUP BY, HAVING & Code Pushdown ​

This companion expands Lesson 7 with deeper technical reasoning and reference implementations.

It covers:

  • aggregate expressions in current CDS View Entities;
  • COUNT, SUM, AVG, MIN, MAX;
  • grouping rules;
  • WHERE vs HAVING;
  • output grain;
  • aggregate result types;
  • amount/currency aggregation;
  • code pushdown;
  • complete guided Reader/Runner source;
  • complete Practice reference solution;
  • troubleshooting;
  • current official SAP references.

1. Current SAP position on aggregation ​

SAP's current Building Data Models with the ABAP Dictionary and ABAP Core Data Services course includes aggregation and grouping as part of CDS data-set processing.

Current SAP learning content explicitly identifies the core CDS aggregate functions:

text
COUNT
MIN
MAX
SUM
AVG

and states that when aggregate expressions are mixed with ordinary fields, a GROUP BY clause is required for the non-aggregated source fields.

The same course places these topics inside its code pushdown unit.

That makes Lesson 7 directly relevant to the current ABAP Cloud backend learning path.

2. Modern CDS only ​

The course continues to use:

abap
define view entity

Do not introduce obsolete DDIC-based CDS syntax such as:

abap
define view

or:

text
legacy generated SQL-view-name annotation

SAP recommends CDS View Entities for current development.

3. Official /DMO/FLIGHT fields ​

SAP's current Flight Reference Scenario documentation lists /DMO/FLIGHT with fields including:

text
carrier_id
connection_id
flight_date
price
currency_code
plane_type_id
seats_max
seats_occupied

The source key is:

text
carrier_id
connection_id
flight_date

This makes it a good aggregation source because several flight rows can belong to the same carrier/connection.

4. Aggregate-expression mental model ​

Start with a set of rows:

text
R1
R2
R3
R4

An aggregate expression returns one calculated value from a set/group of values.

Examples:

text
COUNT → number of rows
SUM   → total
AVG   → mean
MIN   → smallest
MAX   → largest

Without grouping, an aggregate can summarize the entire selected data set.

With grouping:

text
G1 → aggregate
G2 → aggregate
G3 → aggregate

a result is calculated independently for every group.

5. COUNT( * ) ​

COUNT( * ) counts rows.

Current ABAP releases use an INT8 result for COUNT aggregate functions.

For students, the important rule is:

text
COUNT(*)
=
number of source rows in the current group

This is different from:

text
COUNT(field)

which is concerned with values of a specific expression and null handling.

For the guided scenario, COUNT(*) is the clearest fit.

6. SUM ​

SUM is appropriate for additive numeric measures such as:

text
seats
quantities
same-currency amounts

Example:

abap
sum( Flight.seats_occupied )

The result is calculated independently for each group defined by GROUP BY.

7. AVG syntax difference ​

SAP's current CDS learning material highlights an important difference between ABAP SQL and ABAP CDS.

In CDS, the result type for AVG must be specified.

Example:

abap
avg( Flight.seats_occupied as abap.dec( 15, 2 ) )

The type specification belongs inside the AVG aggregate expression.

This is one of the details students should recognize for certification-oriented CDS tasks.

8. Choosing the AVG result type ​

The source seat field is effectively a whole-number quantity.

An average can contain a fraction.

Therefore the course uses:

abap
abap.dec( 15, 2 )

as an easy-to-read teaching result.

The exact precision/scale in a real application should be selected from the domain requirement rather than copied automatically.

9. MIN and MAX ​

MIN and MAX can summarize comparable values.

The guided example uses:

abap
min( Flight.seats_occupied )
max( Flight.seats_occupied )

This avoids a common conceptual error:

text
MAX(value)

does not mean:

text
return the complete row containing MAX(value)

It returns the maximum value only.

Retrieving the associated detail row is a different query problem.

10. GROUP BY requirement ​

Current ABAP CDS rules require grouping when a CDS select list mixes aggregate expressions with ordinary selected fields.

Every selected item outside an aggregate must be represented appropriately in GROUP BY.

Guided projection:

text
CarrierId              normal
ConnectionId           normal
FlightCount            aggregate
TotalSeats             aggregate
OccupiedSeats          aggregate
AverageOccupiedSeats   aggregate
MinimumOccupiedSeats   aggregate
MaximumOccupiedSeats   aggregate

Therefore:

abap
group by
  Flight.carrier_id,
  Flight.connection_id

11. Source names, not current aliases ​

Current CDS View Entity documentation specifies that GROUP BY uses the fields of the data source; alias names defined in the current view's select list are not used as replacements there.

So this is correct:

abap
Flight.carrier_id as CarrierId
...
group by Flight.carrier_id

Do not teach:

abap
group by CarrierId

for the alias introduced by the same select list.

12. Qualified fields with multiple sources ​

If a CDS View Entity uses multiple data sources, current documentation requires grouped fields to be qualified with their data-source name.

Example conceptually:

abap
group by
  Booking.carrier_id,
  Booking.currency_code

This convention also keeps single-source grouped views clearer.

13. Output grain ​

Before writing GROUP BY, state what one result row means.

Guided view:

text
one row
=
one CarrierId + ConnectionId group

Practice view:

text
one row
=
one CarrierId + CurrencyCode group

This makes it easier to detect accidental over-grouping.

14. Over-grouping ​

Suppose the guided view groups by:

text
CarrierId
ConnectionId
FlightDate

Because FlightDate is part of the source key, the grouping becomes much finer.

The aggregates may then summarize only one flight at a time.

The query is syntactically valid but semantically poor for the requirement.

This is a useful certification lesson:

Syntax correctness does not guarantee model correctness.

15. Under-grouping ​

The opposite problem is omitting a normal selected field from GROUP BY.

ADT will report a syntax/model error because the database would not know which value of that non-aggregated field represents the group.

Use the error to reason about the intended grain rather than mechanically adding fields.

16. ADT Quick Fix ​

SAP's current learning material explicitly demonstrates using:

text
Ctrl + 1

to generate a missing GROUP BY clause.

This is a useful productivity technique.

However, students should inspect the generated grouping to ensure it matches the business requirement.

17. WHERE semantics ​

WHERE filters the input row set.

Logical order:

text
FROM
 ↓
WHERE
 ↓
GROUP BY
 ↓
aggregate calculation
 ↓
HAVING

This means WHERE can reduce the rows that participate in aggregation.

Aggregate expressions themselves are not evaluated in WHERE.

18. HAVING semantics ​

HAVING filters after grouping.

It is designed for conditions on groups and can use aggregate expressions.

Example:

abap
having count( * ) >= 2

means:

text
group first
count each group
keep groups with count >= 2

19. HAVING vs layered CDS ​

Two valid models can satisfy a similar business requirement.

Direct aggregation filter ​

text
source
 ↓
GROUP BY
 ↓
HAVING aggregate-condition

Layered filter ​

text
source
 ↓
aggregate CDS
 ↓
second CDS WHERE aggregated-element

The second approach can improve reuse if the unfiltered summary model has value to multiple consumers.

The first approach can be simpler for one direct grouped requirement.

Lesson 7 deliberately shows both concepts and implements HAVING directly.

20. Complete guided aggregate CDS ​

abap
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Flight Statistics'
define view entity ZI_Flight_Stats_###
  as select from /dmo/flight as Flight
{
  key Flight.carrier_id        as CarrierId,
  key Flight.connection_id     as ConnectionId,

      count( * )                as FlightCount,
      sum( Flight.seats_max )   as TotalSeats,
      sum( Flight.seats_occupied )
                                as OccupiedSeats,
      avg( Flight.seats_occupied as abap.dec( 15, 2 ) )
                                as AverageOccupiedSeats,
      min( Flight.seats_occupied )
                                as MinimumOccupiedSeats,
      max( Flight.seats_occupied )
                                as MaximumOccupiedSeats
}
group by
  Flight.carrier_id,
  Flight.connection_id

21. Complete guided HAVING CDS ​

abap
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Busy Flight Connections'
define view entity ZI_Busy_Connection_###
  as select from /dmo/flight as Flight
{
  key Flight.carrier_id          as CarrierId,
  key Flight.connection_id       as ConnectionId,

      count( * )                  as FlightCount,
      sum( Flight.seats_occupied )
                                  as OccupiedSeats
}
group by
  Flight.carrier_id,
  Flight.connection_id
having count( * ) >= 2

22. Complete Reader reference ​

abap
CLASS zcl_flight_stats_reader_### DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.

    TYPES tt_stats TYPE STANDARD TABLE OF zi_flight_stats_###
      WITH EMPTY KEY.

    TYPES tt_busy TYPE STANDARD TABLE OF zi_busy_connection_###
      WITH EMPTY KEY.

    CLASS-METHODS get_all
      RETURNING VALUE(stats) TYPE tt_stats.

    CLASS-METHODS get_busy
      RETURNING VALUE(stats) TYPE tt_busy.

ENDCLASS.


CLASS zcl_flight_stats_reader_### IMPLEMENTATION.

  METHOD get_all.

    SELECT FROM zi_flight_stats_###
      FIELDS *
      ORDER BY CarrierId,
               ConnectionId
      INTO TABLE @stats.

  ENDMETHOD.


  METHOD get_busy.

    SELECT FROM zi_busy_connection_###
      FIELDS *
      ORDER BY CarrierId,
               ConnectionId
      INTO TABLE @stats.

  ENDMETHOD.

ENDCLASS.

23. Complete Runner reference ​

abap
CLASS zcl_cds_l07_### DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.


CLASS zcl_cds_l07_### IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.

    DATA(all_stats) =
      zcl_flight_stats_reader_###=>get_all( ).

    out->write( 'Flight statistics by connection' ).
    out->write( all_stats ).

    DATA(busy_stats) =
      zcl_flight_stats_reader_###=>get_busy( ).

    out->write( 'Connections with at least two flights' ).
    out->write( busy_stats ).

  ENDMETHOD.

ENDCLASS.

24. Code pushdown in this lesson ​

SAP's current learning journey recommends performing calculations on the database when appropriate and explains that CDS expressions enable such calculations to be moved into the data model.

For aggregation, this can avoid transporting large detail sets to ABAP only to reduce them again.

The important word is:

text
appropriate

Do not infer:

text
all business logic belongs in CDS

CDS is excellent for set-oriented data modeling.

RAP/ABAP behavior code will later handle transactional business behavior.

25. Reuse benefit ​

If several consumers need:

text
flight statistics per connection

putting the calculation in a reusable CDS entity gives each consumer the same definition.

Without that model, multiple classes could independently implement slightly different counting/grouping rules.

CDS therefore provides both:

text
pushdown
+
model reuse

26. SQL aggregation vs analytical aggregation annotations ​

This lesson uses SQL aggregation inside a CDS View Entity:

text
GROUP BY
aggregate expressions
HAVING

This produces a fixed grouped result set.

Do not confuse that with analytical-model annotations such as:

abap
@Aggregation.default: #SUM

Those annotations belong to analytical modeling and describe how measures can be aggregated by analytical consumers. They are not required for the SQL-style grouped views in Lesson 7.

27. Monetary aggregation ​

Amounts carry semantic context.

This is not safe:

text
50 EUR
+
60 USD
=
110 ???

So a basic grouped monetary model should either:

  1. group separately by currency; or
  2. convert to a common currency before producing one cross-currency aggregate.

Lesson 7 Practice uses option 1.

28. Practice source /DMO/BOOKING ​

Current SAP Flight Reference Scenario documentation lists /DMO/BOOKING fields including:

text
travel_id
booking_id
booking_date
customer_id
carrier_id
connection_id
flight_date
flight_price
currency_code

The Practice groups by:

text
carrier_id
currency_code

so each aggregate amount has one currency context.

29. Practice target model ​

Recommended result elements:

text
CarrierId
CurrencyCode
BookingCount
TotalBookingValue
LowestBookingValue
HighestBookingValue

The grain is:

one carrier/currency group

not:

one carrier across all currencies

30. Practice reference CDS ​

One valid reference implementation is:

abap
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Booking Statistics'
define view entity ZI_Booking_Stats_###
  as select from /dmo/booking as Booking
{
  key Booking.carrier_id       as CarrierId,
  key Booking.currency_code    as CurrencyCode,

      count( * )                as BookingCount,

      @Semantics.amount.currencyCode: 'CurrencyCode'
      sum( Booking.flight_price )
                                as TotalBookingValue,

      @Semantics.amount.currencyCode: 'CurrencyCode'
      min( Booking.flight_price )
                                as LowestBookingValue,

      @Semantics.amount.currencyCode: 'CurrencyCode'
      max( Booking.flight_price )
                                as HighestBookingValue
}
group by
  Booking.carrier_id,
  Booking.currency_code

The Practice deliberately does not require AVG over FlightPrice. The guided Flight example already teaches the mandatory CDS AVG(... as type) syntax on a simple numeric field. Keeping the Booking exercise to SUM, MIN, and MAX lets students focus on the more important monetary rule: grouped amounts must retain a valid currency context.

If you later need an average monetary amount, use the current SAP amount-expression rules and choose a compatible amount representation deliberately rather than treating a currency amount as an ordinary decimal.

31. Practice HAVING reference ​

A possible second view is:

abap
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Frequent Booking Groups'
define view entity ZI_Booking_Heavy_###
  as select from /dmo/booking as Booking
{
  key Booking.carrier_id    as CarrierId,
  key Booking.currency_code as CurrencyCode,
      count( * )             as BookingCount,

      @Semantics.amount.currencyCode: 'CurrencyCode'
      sum( Booking.flight_price )
                             as TotalBookingValue
}
group by
  Booking.carrier_id,
  Booking.currency_code
having count( * ) >= 2

The exact number of returned groups depends on the demo data in the learner's Trial system.

32. Practice Reader reference ​

abap
CLASS zcl_booking_stats_reader_### DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.

    TYPES tt_stats TYPE STANDARD TABLE OF zi_booking_stats_###
      WITH EMPTY KEY.

    TYPES tt_heavy TYPE STANDARD TABLE OF zi_booking_heavy_###
      WITH EMPTY KEY.

    CLASS-METHODS get_all
      RETURNING VALUE(stats) TYPE tt_stats.

    CLASS-METHODS get_heavy
      RETURNING VALUE(stats) TYPE tt_heavy.

ENDCLASS.


CLASS zcl_booking_stats_reader_### IMPLEMENTATION.

  METHOD get_all.

    SELECT FROM zi_booking_stats_###
      FIELDS *
      ORDER BY CarrierId,
               CurrencyCode
      INTO TABLE @stats.

  ENDMETHOD.


  METHOD get_heavy.

    SELECT FROM zi_booking_heavy_###
      FIELDS *
      ORDER BY CarrierId,
               CurrencyCode
      INTO TABLE @stats.

  ENDMETHOD.

ENDCLASS.

33. Practice Runner reference ​

abap
CLASS zcl_cds_l07_p_### DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.


CLASS zcl_cds_l07_p_### IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.

    DATA(all_stats) =
      zcl_booking_stats_reader_###=>get_all( ).

    out->write( 'Booking statistics by carrier and currency' ).
    out->write( all_stats ).

    DATA(heavy_stats) =
      zcl_booking_stats_reader_###=>get_heavy( ).

    out->write( 'Groups with at least two bookings' ).
    out->write( heavy_stats ).

  ENDMETHOD.

ENDCLASS.

34. Troubleshooting: GROUP BY error ​

Symptom:

text
field must be included in GROUP BY

Check every non-aggregated selected source field.

Do not immediately add every source field.

First restate:

text
one result row should represent ______

Then group only by the dimensions required for that grain.

35. Troubleshooting: alias error in GROUP BY ​

If:

text
CarrierId

is rejected in the same CDS view's GROUP BY, use:

abap
Flight.carrier_id

or the correct source field/data-source alias.

36. Troubleshooting: AVG error ​

The guided Flight view uses a simple numeric source field. Check that AVG specifies a result type:

abap
avg( Flight.seats_occupied as abap.dec( 15, 2 ) )

The result type is mandatory in current ABAP CDS syntax.

Do not use the Booking amount field as the first place to debug AVG; amount fields have additional semantic/type rules.

37. Troubleshooting: HAVING returns no rows ​

The syntax can be correct while the demo data simply has no group meeting the threshold.

Investigate first:

text
Data Preview of base aggregate
actual FlightCount/BookingCount values

Then choose a threshold that matches the exercise data.

Do not change the semantics just to force output.

38. Troubleshooting: mixed currencies ​

If a total combines rows with several currency codes, inspect the grouping.

For a basic same-currency aggregate, make sure:

text
CurrencyCode

is both:

text
selected
+
part of GROUP BY

and amount results reference it with:

abap
@Semantics.amount.currencyCode: 'CurrencyCode'

39. Troubleshooting: using DISTINCT ​

If the requirement asks:

text
how many?
how much in total?
average?
minimum?
maximum?

DISTINCT alone is not the tool.

Use aggregate expressions and define the intended groups.

40. Certification-style reasoning prompts ​

Students should be able to answer without looking at source:

text
Why GROUP BY?
Why is FlightDate omitted?
Why does AVG specify a type?
Why HAVING instead of WHERE?
Why group monetary totals by CurrencyCode?
Why is database aggregation an example of code pushdown?

These reasoning questions are more valuable than memorizing one exact view name.

41. Current official SAP references ​

SAP Learning — Processing Data Sets in CDS Views
https://learning.sap.com/courses/building-data-models-with-the-abap-dictionary-and-abap-core-data-services/including-aggregations-joins-and-unions-in-cds-views_fa94fdc8-20e2-4fb4-b980-5323314a47bf

SAP Learning — Implementing SQL Expressions in CDS Views
https://learning.sap.com/courses/building-data-models-with-the-abap-dictionary-and-abap-core-data-services/implementing-sql-expressions-in-cds-views_a9962c08-c5df-46a6-ba30-a74acf5c86b2

SAP Learning — Building Data Models with ABAP Dictionary and ABAP CDS
https://learning.sap.com/courses/building-data-models-with-the-abap-dictionary-and-abap-core-data-services

SAP Help — ABAP CDS View Entities
https://help.sap.com/doc/abapdocu_latest_index_htm/latest/en-US/abencds_v2_views.htm

SAP Help — CDS View Entity GROUP BY
https://help.sap.com/doc/abapdocu_latest_index_htm/latest/en-US/ABENCDS_GROUP_BY_V2.html

SAP Help — Aggregate Expressions
https://help.sap.com/docs/abap-cloud/abap-keyword/aggregate-expression

SAP Help — ABAP Flight Reference Scenario
https://help.sap.com/docs/CP/c0d02c4330c34b3abca88bdd57eaccfc/16acbb3fa7434beeb3c771397db5997f.html

SAP Help — Handling Amounts and Quantities in ABAP CDS
https://help.sap.com/docs/abap-cloud/abap-data-models/handling-amounts-and-quantities-in-abap-cds

SAP Help — Query Modeling
https://help.sap.com/docs/abap-cloud/abap-data-models/query-modeling

SAP Help — Implementing Aggregations (Analytical Modeling)
https://help.sap.com/docs/abap-cloud/abap-analytics/implement-aggregations