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

Until now, most CDS views in this course returned one result row for one source row.

For example, /DMO/FLIGHT contains individual flights:

Carrier + Connection + Flight Date
                ↓
           one flight row

But many backend requirements ask different questions:

How many flights exist per connection?
How many seats are available in total?
What is the average occupancy count?
What is the smallest or largest value in a group?
Which connections have at least two flights?

Those are aggregation questions.

Instead of moving all detailed rows to ABAP and calculating the answer in a loop, the database can perform the grouping and calculations first.

Detailed database rows
        ↓
GROUP BY
        ↓
COUNT / SUM / AVG / MIN / MAX
        ↓
HAVING
        ↓
Small summarized result
        ↓
ABAP consumer

This lesson completes the CDS foundation before the course moves to your own persistence and RAP.

---

Lesson information

Duration	About 1–2 course days
Environment	SAP BTP ABAP Trial
Development tool	Eclipse with ABAP Development Tools (ADT)
Prerequisites	Lessons 1–6
Guided source	/DMO/FLIGHT
Aggregate CDS	ZI_FLIGHT_STATS_###
HAVING CDS	ZI_BUSY_CONNECTION_###
Reader	ZCL_FLIGHT_STATS_READER_###
Runner	ZCL_CDS_L07_###
Practice source	/DMO/BOOKING

Replace ### with your own student/group suffix.

---

Learning objectives

After completing this lesson, you should be able to:

• explain what aggregation does;
• use COUNT, SUM, AVG, MIN, and MAX in CDS;
• explain why AVG requires an explicit result type in ABAP CDS;
• use GROUP BY correctly;
• explain why non-aggregated selected fields belong in GROUP BY;
• explain why aliases from the current select list are not used in GROUP BY;
• distinguish WHERE from HAVING;
• use an aggregate expression in HAVING;
• understand that COUNT( * ) counts rows in each group;
• preserve amount/currency semantics when aggregating monetary values;
• explain why values from different currencies must not be blindly summed together;
• explain code pushdown in practical terms;
• consume an aggregated CDS View Entity from ABAP;
• recognize when aggregation belongs in CDS/database processing rather than an ABAP loop.

---

1. Start by looking at the detail data

Open:

/DMO/FLIGHT

Use Data Preview.

The table contains fields including:

carrier_id
connection_id
flight_date
price
currency_code
plane_type_id
seats_max
seats_occupied

The key is:

carrier_id
+
connection_id
+
flight_date

So these rows represent individual flights.

---

2. A detail query answers a row-level question

A normal query could return:

Carrier  Connection  Date        Seats Max  Occupied
LH       0400        ...         ...        ...
LH       0400        ...         ...        ...
LH       0401        ...         ...        ...

Each source flight remains visible separately.

That is useful when the requirement is:

Show me the individual flights.

But suppose the requirement changes to:

Give me one summary row per carrier and connection.

Now we need aggregation.

---

3. What aggregation does

Aggregation reduces multiple input rows into calculated results.

Example source:

LH 0400  100 occupied
LH 0400  120 occupied
LH 0401   80 occupied

Grouped by:

CarrierId + ConnectionId

could become:

LH 0400  FlightCount = 2  OccupiedSeats = 220
LH 0401  FlightCount = 1  OccupiedSeats = 80

The detailed rows still exist in the database.

The CDS query returns a summary result.

---

4. The five aggregate functions in this lesson

We will use:

COUNT
SUM
AVG
MIN
MAX

COUNT

Counts rows or non-null values.

count( * )

means:

Count the rows in the current group.

SUM

Adds numeric values.

sum( Flight.seats_occupied )

AVG

Calculates an average.

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

ABAP CDS requires the result type to be specified for AVG.

MIN

Returns the smallest value in the group.

min( Flight.seats_occupied )

MAX

Returns the largest value in the group.

max( Flight.seats_occupied )

---

5. Experiment first in SQL Console

Before creating CDS, open the SQL Console and try an aggregated ABAP SQL query.

Use:

SELECT FROM /dmo/flight
  FIELDS carrier_id,
         connection_id,
         COUNT( * )           AS flight_count,
         SUM( seats_occupied ) AS occupied_seats
  GROUP BY carrier_id,
           connection_id
  ORDER BY carrier_id,
           connection_id

The exact output depends on the current demo data in your system.

The important change is the shape of the result:

many flight rows
      ↓
one row per carrier/connection group

---

6. Why GROUP BY is necessary

Look at this result request:

CarrierId
ConnectionId
FlightCount

FlightCount is aggregated:

count( * )

but:

CarrierId
ConnectionId

are normal selected fields.

SQL needs to know which rows belong together before it can calculate the aggregate.

So we say:

group by
  Flight.carrier_id,
  Flight.connection_id

---

7. The core GROUP BY rule

When a CDS select list mixes:

aggregate expressions
+
normal fields

the normal selected source fields must be included in GROUP BY.

Conceptually:

Selected field
     ↓
Is it inside an aggregate function?
     ├─ Yes → do not GROUP BY that aggregate result
     └─ No  → it belongs in GROUP BY

For our model:

CarrierId        → GROUP BY
ConnectionId     → GROUP BY
FlightCount      → aggregate
TotalSeats       → aggregate
OccupiedSeats    → aggregate
AverageOccupied  → aggregate
MinimumOccupied  → aggregate
MaximumOccupied  → aggregate

---

8. Create the aggregate CDS View Entity

Create a new Data Definition.

Use:

Name:        ZI_FLIGHT_STATS_###
Description: Flight Statistics

Choose a CDS View Entity template.

---

9. Define ZI_FLIGHT_STATS_###

Use:

@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

Replace ###.

---

10. Read the view from top to bottom

The source is:

as select from /dmo/flight as Flight

The grouping dimensions are:

CarrierId
ConnectionId

The measures/calculated summaries are:

FlightCount
TotalSeats
OccupiedSeats
AverageOccupiedSeats
MinimumOccupiedSeats
MaximumOccupiedSeats

The output grain is now:

one row per CarrierId + ConnectionId

That phrase — output grain — is useful when reasoning about grouped queries.

---

11. Why FlightDate disappeared

The source table key includes:

FlightDate

but the aggregate view does not expose it.

That is intentional.

If you grouped by:

CarrierId
ConnectionId
FlightDate

you would move back toward one group per individual flight.

The requirement is instead:

summarize all flights belonging to the same carrier/connection.

So FlightDate is not part of the grouping dimensions.

---

12. CDS keys after aggregation

The view declares:

key CarrierId
key ConnectionId

Because those are exactly the grouping dimensions, the result has at most one row for each carrier/connection combination.

Remember the rule from Lesson 2:

A CDS key expresses the semantic identity of the CDS result. It does not create or change a database-table primary key.

---

13. Why GROUP BY uses source-field names

The select list defines aliases:

Flight.carrier_id    as CarrierId
Flight.connection_id as ConnectionId

But the GROUP BY uses:

Flight.carrier_id,
Flight.connection_id

not:

CarrierId
ConnectionId

For the current CDS View Entity, the aliases created in the select list are not used as replacements in the same view's GROUP BY clause.

Use the source fields.

---

14. ADT can help generate GROUP BY

A useful ADT workflow is:

1. write the element list containing aggregates;
2. run syntax check;
3. place the cursor where ADT reports the missing grouping;
4. press:

Ctrl + 1

5. if offered, choose the Quick Fix to add the GROUP BY clause;
6. inspect the generated fields rather than accepting them blindly.

This is also useful certification practice: use the IDE productively, then verify the result.

---

15. Check and activate

Run:

Ctrl + F2
Ctrl + F3

Then open Data Preview:

F8

You should now see summarized rows.

The number of result rows should normally be smaller than the number of /DMO/FLIGHT detail rows because multiple flight dates can belong to one carrier/connection group.

---

16. Understand COUNT( * )

This element:

count( * ) as FlightCount

counts the rows in each group.

If a carrier/connection group contains two flight rows:

FlightCount = 2

If another group contains five:

FlightCount = 5

In current ABAP, COUNT aggregate results use an integer result type suitable for row counts; you should treat the result as a count, not as one of the source business fields.

---

17. Understand SUM

This:

sum( Flight.seats_max ) as TotalSeats

means:

Add seats_max across all flights in the current group.

This:

sum( Flight.seats_occupied ) as OccupiedSeats

means:

Add occupied seats across the same group.

Example:

Flight 1: 180 occupied
Flight 2: 160 occupied

SUM = 340

---

18. Understand AVG

This:

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

calculates the average occupied-seat count per flight in the group.

For example:

100
120
80

average:

100.00

ABAP CDS requires you to specify the desired result type inside AVG.

That is why we use:

as abap.dec( 15, 2 )

inside the aggregate call.

---

19. Why the AVG result type is explicit

An average can produce decimal places even when the source values are integers.

For example:

100 + 101
---------
    2

= 100.5

The explicit type makes the required result representation clear.

Do not confuse:

avg( field as abap.dec( 15, 2 ) )

with a normal CDS CAST expression.

This is the result-type syntax of AVG in ABAP CDS.

---

20. Understand MIN and MAX

These elements:

min( Flight.seats_occupied ) as MinimumOccupiedSeats,
max( Flight.seats_occupied ) as MaximumOccupiedSeats

answer:

What was the smallest occupied-seat value in this group?
What was the largest occupied-seat value in this group?

They do not tell you which flight date produced that value.

That distinction matters.

Aggregation summarizes values; it does not automatically return the full source row associated with the minimum or maximum.

---

21. Filtering before vs after aggregation

Now consider two requirements.

Requirement A:

Before grouping, ignore certain individual flight rows.

Requirement B:

After grouping, keep only groups whose calculated FlightCount meets a threshold.

These require different clauses.

WHERE
  ↓
filters source rows before grouping

HAVING
  ↓
filters groups after aggregation

---

22. WHERE happens before grouping

Conceptually:

Source rows
    ↓
WHERE
    ↓
remaining rows
    ↓
GROUP BY
    ↓
aggregate functions

A WHERE clause does not filter based on a result such as:

FlightCount

because that aggregate result does not exist until after the rows have been grouped.

---

23. HAVING happens after grouping

Conceptually:

Source rows
    ↓
GROUP BY
    ↓
COUNT / SUM / AVG / ...
    ↓
HAVING
    ↓
remaining groups

HAVING can therefore contain aggregate expressions.

For example:

having count( * ) >= 2

means:

Return only groups that contain at least two source flight rows.

---

24. Create a second CDS using HAVING

Create:

ZI_BUSY_CONNECTION_###

Description:

Busy Flight Connections

Use:

@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

Activate and preview it.

---

25. Why repeat count( * ) in HAVING?

The select list contains:

count( * ) as FlightCount

but the filtering condition uses:

having count( * ) >= 2

This directly expresses the aggregate condition used to keep or remove a group.

Think of it as:

calculate the count for each group
then keep groups whose count is at least 2

---

26. WHERE vs HAVING summary

Clause	Filters	Evaluation stage	Aggregate expressions?
WHERE	individual source rows	before grouping	No
HAVING	grouped result	after grouping	Yes

A useful question is:

Am I filtering rows, or am I filtering a calculated group?

---

27. Could we use CDS-on-CDS instead?

Yes.

You already learned layered CDS modeling in Lesson 3.

You could define:

ZI_FLIGHT_STATS_###
        ↓
second CDS view
        ↓
WHERE FlightCount >= 2

Once FlightCount is a normal element of the lower CDS entity, an upper view can filter it with WHERE.

That is a valid layered design.

This lesson uses HAVING because the requirement can be expressed directly in the same aggregation query.

So there are two different modeling choices:

same aggregation query
→ HAVING

higher CDS layer over an aggregated CDS
→ WHERE on the already-calculated element

Choose the design that gives the clearest reusable model.

---

28. Code pushdown

Now compare two approaches.

Approach A — retrieve detail rows and calculate in ABAP

Database
  ↓
all flight rows
  ↓
ABAP application server
  ↓
LOOP
  ↓
manual grouping/counting/summing

Approach B — aggregate in CDS

Database
  ↓
GROUP BY / aggregates
  ↓
summary rows only
  ↓
ABAP application server

Approach B demonstrates code pushdown.

The calculation is expressed close to the data using CDS/SQL capabilities so the database can execute set-oriented processing.

---

29. Why pushdown matters

Potential benefits include:

less detail data transferred to ABAP
set-oriented processing
reuse of the calculation in multiple consumers
simpler ABAP consumer code
use of SAP HANA/database query processing

The principle is not:

Put every possible calculation in CDS.

The better principle is:

Perform data-intensive filtering, joining, grouping, and calculation in the database layer when that is the appropriate place for the requirement.

Business behavior and transactional rules will later belong in RAP/ABAP behavior logic.

---

30. A poor ABAP approach

Imagine reading every flight first:

SELECT FROM /dmo/flight
  FIELDS *
  INTO TABLE @DATA(flights).

and then writing application-server logic to:

group by carrier
then connection
count rows
sum seats
find min
find max
calculate average

ABAP can perform such calculations.

But if the requirement is fundamentally a database aggregation, retrieving all detail rows only to reduce them again in ABAP is usually unnecessary.

---

31. CDS gives the consumer a reusable summary model

Now any ABAP consumer can read:

ZI_FLIGHT_STATS_###

without knowing how the aggregates are calculated.

Architecture:

Runner
  ↓
Reader
  ↓
ABAP SQL
  ↓
ZI_FLIGHT_STATS_###
  ↓
GROUP BY + aggregate functions
  ↓
/DMO/FLIGHT

This is the same separation you have used throughout the course.

---

32. Create the Reader

Create:

ZCL_FLIGHT_STATS_READER_###

Use:

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.

Replace ###.

---

33. The CDS entity is a global ABAP type

This line:

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

works because an activated CDS View Entity can be used as a global structured type in ABAP.

You already used this technique in earlier CDS lessons.

The difference is that the structure now contains aggregate results rather than detailed source-row fields.

---

34. Create the Runner

Create:

ZCL_CDS_L07_###

Implement:

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.

Activate and run:

F9

---

35. Notice what the Runner does not contain

The Runner does not contain:

LOOP AT flights
manual counters
manual sums
manual averages
manual grouping tables

It asks for the summary model.

ABAP receives the result
rather than rebuilding the database calculation

That is one of the practical outcomes of code pushdown.

---

36. SQL aggregation is not the same as an analytical CDS model

This lesson uses SQL-style aggregation directly in a CDS View Entity:

GROUP BY
COUNT / SUM / AVG / MIN / MAX
HAVING

You may later encounter analytical annotations such as:

@Aggregation.default: #SUM

Those belong to analytical modeling, where a consumer can request aggregation dynamically from an analytical model.

That is a different topic.

Here, the grouping grain is fixed by the CDS query itself:

CarrierId + ConnectionId

and the database returns an already-grouped result.

Do not add analytical annotations to this lesson's views.

---

37. Aggregating money requires semantic care

Seats are easy to add:

180 seats + 200 seats

Money is different:

100 EUR + 100 USD

A raw numeric total of:

200

has no meaningful single currency.

So when aggregating amount fields, the currency dimension must be handled correctly.

---

38. Correct approach for same-currency groups

Suppose a source contains:

CarrierId
FlightPrice
CurrencyCode

A safe basic grouping is:

CarrierId + CurrencyCode

Then:

AA + USD
LH + EUR
LH + USD

become different groups.

Within each group, the amount values have the same currency context.

---

39. Amount semantics still matter after SUM

An aggregated monetary element should preserve the amount/currency relationship.

Conceptually:

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

and:

CurrencyCode

must also participate in the grouping.

You will implement this in the Practice assignment.

---

40. Grouping by currency is not currency conversion

This:

GROUP BY CurrencyCode

prevents different currencies from being mixed in one total.

It does not convert currencies.

If the requirement were:

Show one total in EUR across bookings stored in several currencies.

then the design would need an explicit currency-conversion strategy before or as part of aggregation.

That is a different requirement.

---

41. Common mistake: adding every source field to GROUP BY

Suppose you begin with:

CarrierId
ConnectionId
FlightDate

and add all three to GROUP BY just to make the syntax checker happy.

You may accidentally produce:

one group per flight

instead of:

one group per connection

The correct question is not:

Which fields can I add until the error disappears?

The correct question is:

What should one output row represent?

Then choose the grouping dimensions accordingly.

---

42. Common mistake: using DISTINCT instead of aggregation

DISTINCT and GROUP BY solve different problems.

DISTINCT removes duplicate result rows.

Aggregation calculates values across groups.

Do not write:

DISTINCT

as a substitute for:

COUNT
SUM
GROUP BY

---

43. Common mistake: trying to use WHERE on an aggregate

This idea is wrong inside the same aggregation query:

WHERE FlightCount >= 2

because FlightCount has not yet been calculated at the row-filtering stage.

Use:

having count( * ) >= 2

or create a higher CDS layer and apply WHERE to the already-calculated lower-view element.

---

44. Common mistake: grouping by the current select-list alias

Inside the same CDS View Entity, do not write:

group by CarrierId,
         ConnectionId

when those names are aliases introduced by:

Flight.carrier_id as CarrierId

Use the source fields:

group by Flight.carrier_id,
         Flight.connection_id

---

45. Common mistake: forgetting the AVG type

This is incomplete for ABAP CDS:

avg( Flight.seats_occupied )

Specify the result type:

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

---

46. Common mistake: summing amounts across currencies

Do not create a single:

TotalBookingValue

across rows that may contain:

EUR
USD
GBP

unless the amounts have first been converted to a common currency.

For the basic practice exercise, group by currency.

---

47. Certification connection

SAP's current ABAP data-modeling learning content explicitly includes:

aggregations
GROUP BY
COUNT
MIN
MAX
SUM
AVG
code pushdown

and teaches aggregation as part of processing data sets in CDS views.

This lesson therefore closes an important gap before persistence and RAP.

A practical assessment may not ask:

Define code pushdown.

It may instead give you a requirement where the correct implementation is:

one CDS query with aggregation

rather than:

read everything + calculate manually in ABAP

---

48. Knowledge check

1. What does aggregation do?

Show answer

It calculates summary values from multiple source rows, optionally separately for groups defined by GROUP BY.

2. What does COUNT( * ) count?

Show answer

The rows in the current result/group.

3. Why do CarrierId and ConnectionId belong in GROUP BY?

Show answer

They are selected as normal fields outside aggregate functions and define the intended output grain: one summary row per carrier/connection.

4. Why is FlightDate not grouped in ZI_FLIGHT_STATS_###?

Show answer

The requirement is to combine multiple flights/dates into one connection summary. Grouping by flight date would create finer groups and defeat that summary requirement.

5. What is the difference between WHERE and HAVING?

Show answer

WHERE filters source rows before grouping; HAVING filters groups after aggregation and can use aggregate expressions.

6. Why does AVG specify a result type?

Show answer

ABAP CDS requires an explicit result type for AVG, and an average can require decimal precision that differs from the source value.

7. What does code pushdown mean here?

Show answer

The grouping and calculations are expressed in CDS so they are executed in database query processing rather than retrieving all detail rows and manually reproducing the same set operation in ABAP.

8. Why must currency be considered when summing amounts?

Show answer

A numeric sum across different currencies has no single monetary meaning. Basic aggregation should keep currencies separate or explicitly convert amounts to a common target currency.

---

49. Lesson summary

You extended your CDS knowledge from row-level modeling to set-level summaries.

You learned:

COUNT
SUM
AVG
MIN
MAX
GROUP BY
HAVING
WHERE vs HAVING
output grain
aggregate result typing
amount/currency grouping
code pushdown

The architecture is:

/DMO/FLIGHT
      ↓
GROUP BY Carrier + Connection
      ↓
COUNT / SUM / AVG / MIN / MAX
      ↓
ZI_FLIGHT_STATS_###
      ↓
Reader
      ↓
Runner

For filtered groups:

/DMO/FLIGHT
      ↓
GROUP BY
      ↓
COUNT
      ↓
HAVING count(*) >= 2
      ↓
ZI_BUSY_CONNECTION_###

The central idea is:

When a requirement asks for a database summary, model the set operation close to the data instead of automatically moving all detail rows to ABAP and rebuilding the calculation there.

---

50. What comes next?

The first seven lessons used persistence owned by SAP's /DMO/ reference scenario.

Next, that changes.

Lesson 8 introduces:

your own Domain
      ↓
your own Data Element
      ↓
your own database table
      ↓
controlled INSERT / UPDATE / DELETE

That persistence will then become the foundation for your first managed RAP Business Object.