Aggregate Functions, GROUP BY, and HAVING in ABAP CDS View Entities

In business applications we rarely want to display every individual transaction.

Instead, business users usually ask questions such as:

• Total Sales Amount
• Total Number of Orders
• Average Delivery Time
• Highest Sales Order Value
• Lowest Product Price

These questions cannot be answered by returning individual rows.

They require summarizing data across multiple records. This process is known as Aggregation.

Suppose the Sales Order Item table contains the following data.

Sales Order    Item    Net Amount

1001           10      200

1001           20      300

1001           30      500

1002           10      150

If the business asks:

What is the total value of Sales Order 1001?

Returning individual rows is not sufficient.

The database must calculate:

Sales Order    Total Amount

1001           1000

1002           150

define view entity ZI_TotalSales

  as select from I_SalesDocumentItem
{
    key SalesDocument,

    sum( NetAmount ) as TotalAmount
}
group by
    SalesDocument;

This CDS View groups all items belonging to the same Sales Document and calculates the total Net Amount.

The moment you introduce an aggregate function such as SUM(), COUNT(),AVG(), MIN() orMAX(), the database must know how the records should be grouped before performing the calculation.

This is exactly the purpose of the GROUP BY clause.

GROUP BY tells SAP HANA how to partition the source rows before calculating aggregate values.

Consider the following data.

Sales Order    Item    Net Amount

1001           10      200

1001           20      300

1001           30      500

1002           10      150

1002           20      350

If we write:

define view entity ZI_TOTAL

  as select from I_SalesDocumentItem
{
    key SalesDocument,

    sum( NetAmount ) as TotalAmount
}

The compiler immediately raises an error.

Every non-aggregated field appearing in the SELECT list must also appear in the GROUP BY clause.

define view entity ZI_TOTAL

  as select from I_SalesDocumentItem
{
    key SalesDocument,

    sum( NetAmount ) as TotalAmount
}
group by
    SalesDocument;

Now SAP HANA creates one group for every Sales Document and calculates the total amount within each group.

1001    200

1001    300

1001    500

1002    150

1002    350

GROUP BY first separates the rows.

Group 1001

200

300

500

──────────

Group 1002

150

350

The aggregate function is then applied to each group individually.

Sales Order    Total

1001           1000

1002            500

A group is not limited to a single column.

Multiple columns can be used to create more granular groups.

define view entity ZI_TOTAL

  as select from I_SalesDocumentItem
{
    key SalesOrganization,

    key TransactionCurrency,

    sum( NetAmount ) as TotalAmount
}
group by

    SalesOrganization,

    TransactionCurrency;

Here, SAP HANA creates one group for every unique combination of Sales Organization and Transaction Currency.

This rule is one of the most important concepts in SQL and ABAP CDS.

{
    key SalesDocument,

    Material,

    sum( NetAmount ) as Total
}
group by

    SalesDocument;

The field Material appears in the SELECT list but is missing from GROUP BY.

SAP cannot determine which Material should be returned for each Sales Document because a Sales Order may contain many Materials.

group by

    SalesDocument,

    Material;

One of the most common questions in SQL and ABAP CDS is:

Why do we need HAVING when we already have WHERE?

The answer lies in understanding when each clause is executed.

The WHERE clause filters individual rows before any grouping or aggregation takes place, whereas the HAVING clause filters the aggregated groups after the aggregate functions have been calculated.

1. FROM

↓

2. WHERE

↓

3. GROUP BY

↓

4. Aggregate Functions

(SUM, COUNT, AVG...)

↓

5. HAVING

↓

6. SELECT

Understanding this execution order explains almost every rule related to aggregation in ABAP CDS.

Assume the source data contains the following Sales Order Items.

Sales Order    Date         Net Amount

1001           2026-01-02      20000

1001           2026-01-05      30000

1002           2026-01-03      10000

1002           2026-01-07      15000

The business requirement is:

Show only Sales Orders created in 2026 whose total value exceeds 50,000.

Let's walk through the execution exactly as SAP HANA performs it.

WHERE

↓

Keep only rows created on or after

2026-01-01

GROUP BY

↓

Create one group for each

Sales Document

SUM()

↓

Calculate TotalAmount

for every group

HAVING

↓

Remove groups whose

TotalAmount <= 50000

Many beginners attempt to write:

where

sum( NetAmount ) > 50000

This is impossible because the SUM() has not been calculated when the WHERE clause executes.

At the WHERE stage, the compiler is still processing individual rows.

Consider the following CDS View.

define view entity ZI_DEMO_AGGREGATE

  as select from I_SalesDocumentItem
{
    key SalesDocument,

    sum( get_numeric_value( NetAmount ) ) as TotalAmount
}
where

    CreationDate >= '20260101'

group by

    SalesDocument

having

    sum( NetAmount ) > 50000;

At first glance, this CDS looks perfectly valid.

However, from an ABAP Cloud and SAP Technical Architect perspective, there are several important design issues.

In the SELECT list we calculate:

sum( get_numeric_value( NetAmount ) )

But the HAVING clause evaluates:

sum( NetAmount ) > 50000

These are not the same expressions.

The SELECT removes currency semantics by calling GET_NUMERIC_VALUE(), whereas the HAVING clause still aggregates the original CURR field.

Although some releases accept this syntax, it is inconsistent and should be avoided.

The original field NetAmount is a CURR field with an associated currency.

NetAmount

After calling:

get_numeric_value( NetAmount )

the result becomes a plain numeric value.

The currency information is intentionally removed.

The value is now suitable for mathematical calculations, but it is no longer a business amount.

Suppose your CDS returns:

Sales Order     Total

1001            55000

1002            70000

What exactly does the value 55,000 represent?

• 55,000 USD?
• 55,000 EUR?
• 55,000 INR?

Once currency semantics are removed, the business meaning disappears.

Imagine the following data.

Sales Order    Currency    Amount

1003           USD         20000

1003           EUR         40000

Aggregating these values using GET_NUMERIC_VALUE() produces:

Sales Order

1003

Total

60000

This total has no business meaning because two different currencies have been added together.

If the aggregated value is intended to remain a business amount, preserve the currency semantics.

define view entity ZI_DEMO_AGGREGATE

  as select from I_SalesDocumentItem
{
    key SalesDocument,

    TransactionCurrency,

    @Semantics.amount.currencyCode: 'TransactionCurrency'
    sum( NetAmount ) as TotalAmount
}
group by

    SalesDocument,

    TransactionCurrency

having

    sum( NetAmount ) > 50000;

This approach keeps the amount and its currency together, preserving the business meaning.

If the requirement is purely mathematical, use GET_NUMERIC_VALUE(), but perform business filtering in a second CDS View.

define view entity ZI_DEMO_AGGREGATE

  as select from I_SalesDocumentItem
{
    key SalesDocument,

    sum(
        get_numeric_value( NetAmount )
    ) as TotalAmount
}
group by

    SalesDocument;

define view entity ZC_DEMO_AGGREGATE

  as select from ZI_DEMO_AGGREGATE
{
    *
}
where

    TotalAmount > 50000;

This layered approach is clean, reusable and aligns well with ABAP Cloud development principles.

If business logic requires a specific order, perform it in Open SQL.

SELECT *

FROM ZI_TOTAL

ORDER BY SalesDocument,
         TotalAmount DESC

INTO TABLE @DATA(lt_total).

This is the correct place to define ordering because the ABAP program is the consumer of the CDS View.

Suppose a Fiori List Report displays the aggregated Sales Orders.

When the user clicks the Total Amount column header and selects Descending, the UI sends an OData request similar to:

GET

.../SalesOrders

?$orderby=TotalAmount desc

The OData framework and RAP automatically translate this into an efficient SQL statement.

The CDS View itself remains unchanged.

Aggregate functions are one of the biggest advantages of SAP HANA's Code Pushdown philosophy.

Instead of transferring millions of records to the application server and calculating totals in ABAP, SAP HANA performs the aggregation directly in the database.

However, good performance depends on how the CDS View is designed.