ABAP Developmentintermediate

Open SQL Part 6: Aggregates, GROUP BY and HAVING

Key takeaway

Let the database compute totals: COUNT, SUM, MIN, MAX, AVG, GROUP BY and HAVING, DISTINCT, and summing amounts correctly per currency — instead of loops and COLLECT in ABAP.

Explanation

📌 KEY POINTS
✅ Aggregate functions: COUNT( * ), COUNT( DISTINCT f ), SUM, MIN, MAX, AVG.
✅ Every selected non-aggregated field must be in GROUP BY.
✅ HAVING filters groups (after aggregation); WHERE filters rows (before).
⚠️ Never SUM amounts across currencies — group by the currency field too.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 1–5 read rows. Often you only need numbers — seats per airline, revenue per month. The database computes these far faster than a loop in ABAP.

━━━━━━━━━━━━━━━━━━━━
▶ 1. AGGREGATE FUNCTIONS

COUNT( * ) — number of rows; COUNT( DISTINCT customid ) — number of different values.
SUM( forcuram ), MIN( fldate ), MAX( price ), AVG( price ) — AVG returns a float-like result; use carefully for amounts.

━━━━━━━━━━━━━━━━━━━━
▶ 2. GROUP BY
SELECT carrid SUM( seatsocc ) FROM sflight INTO TABLE gt_seats GROUP BY carrid.
Each selected field that is not aggregated must appear in GROUP BY. The target structure must match the selected columns (use aliases in strict syntax: SUM( seatsocc ) AS occupied).

━━━━━━━━━━━━━━━━━━━━
▶ 3. HAVING
Filter groups: … GROUP BY carrid HAVING SUM( seatsocc ) > 10000.
WHERE runs first (which rows go into groups), HAVING afterwards (which groups remain).

━━━━━━━━━━━━━━━━━━━━
▶ 4. CURRENCIES AND UNITS
SUM( forcuram ) over EUR and USD bookings is meaningless. Always GROUP BY the currency (forcurkey) or convert first. The same applies to quantities and units.

━━━━━━━━━━━━━━━━━━━━
▶ 5. BUFFER AND AGGREGATES
Aggregate statements bypass the table buffer — fine for large tables, but for small buffered tables consider reading and summing in ABAP.

━━━━━━━━━━━━━━━━━━━━
▶ 6. WHY IN THE DATABASE?
Reading 2 million bookings to sum them in ABAP transfers 2 million rows; SUM … GROUP BY transfers a few dozen. On HANA, aggregation is extremely fast (column store).

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP
Run the code example: bookings per airline, revenue per airline and currency, airlines with more than N bookings (HAVING), and distinct customers.

✅ CHECKPOINT: you compute totals in SQL and never sum across currencies.

Code example

ABAP Code

REPORT zosql_part6. TYPES: BEGIN OF ty_rev,         carrid    TYPE s_carr_id,         forcurkey TYPE s_curr,         revenue   TYPE s_f_cur_pr,         bookings  TYPE i,       END OF ty_rev,       BEGIN OF ty_busy,         carrid   TYPE s_carr_id,         bookings TYPE i,       END OF ty_busy,       BEGIN OF ty_range,         carrid       TYPE s_carr_id,         connid       TYPE s_conn_id,         first_flight TYPE s_date,         last_flight  TYPE s_date,       END OF ty_range. DATA: gt_rev   TYPE STANDARD TABLE OF ty_rev,      gs_rev   TYPE ty_rev,      gt_busy  TYPE STANDARD TABLE OF ty_busy,      gt_cust  TYPE STANDARD TABLE OF ty_busy,      gt_range TYPE STANDARD TABLE OF ty_range,      gv_cnt   TYPE i. START-OF-SELECTION.* ===== 1. Count rows =====  SELECT COUNT( * ) FROM sbook INTO gv_cnt WHERE carrid = 'LH'.  WRITE: / 'LH bookings:', gv_cnt. * ===== 2. SUM per airline AND currency =====  SELECT carrid forcurkey SUM( forcuram ) COUNT( * )    FROM sbook    INTO TABLE gt_rev    WHERE cancelled = space    GROUP BY carrid forcurkey.                   " currency in GROUP BY!  LOOP AT gt_rev INTO gs_rev.    WRITE: / gs_rev-carrid, gs_rev-revenue CURRENCY gs_rev-forcurkey,             gs_rev-forcurkey, gs_rev-bookings.  ENDLOOP. * ===== 3. HAVING: only airlines with many bookings =====  SELECT carrid COUNT( * )    FROM sbook    INTO TABLE gt_busy    WHERE cancelled = space    GROUP BY carrid    HAVING COUNT( * ) > 1000. * ===== 4. Distinct customers per airline =====  SELECT carrid COUNT( DISTINCT customid )    FROM sbook    INTO TABLE gt_cust    GROUP BY carrid. * ===== 5. Earliest and latest flight date per connection =====  SELECT carrid connid MIN( fldate ) MAX( fldate )    FROM sflight    INTO TABLE gt_range    GROUP BY carrid connid. * AVOID: SELECT * FROM sbook INTO TABLE gt_all. LOOP ... COLLECT ... ENDLOOP.*        -> millions of rows transferred just to compute a few totals* 💡 Strict syntax adds aliases: SELECT carrid, COUNT( * ) AS bookings ... INTO TABLE @DATA(lt_busy).

Real project scenario

A monthly revenue report read all bookings of the year (8 million rows) and summed them in ABAP with COLLECT. It needed 40 minutes, and the totals mixed EUR and USD amounts in one column, which nobody noticed because the numbers looked plausible.
Rewriting the logic as SUM … GROUP BY airline, month and currency reduced runtime to seconds and exposed the currency mix; the output was changed to show one line per currency.

Common mistakes

Summing in ABAP what the database can aggregate.
Summing across currencies or units.
Selecting non-aggregated fields missing from GROUP BY.
Using HAVING where WHERE should filter rows.
Expecting aggregates to use the table buffer.

Best practices

Aggregate in SQL; transfer only results.
Always group by currency/unit for amounts/quantities.
Filter rows with WHERE, groups with HAVING.
Use aliases and matching target structures.
Test with multi-currency data.

Interview angle

Questions: "WHERE vs HAVING?", "Why aggregate in the database?", "What must be in GROUP BY?"
Strong answer: WHERE filters rows before grouping, HAVING filters groups after aggregation; all non-aggregated selected fields must be in GROUP BY; aggregating in the DB avoids transferring millions of rows (especially fast on HANA); always group amounts by currency; aggregates bypass the buffer.

Practice this lesson

Quick check

1. Which clause filters groups after aggregation?

2. A query sums booking amounts per airline. Which GROUP BY is correct?

3. Aggregate functions like SUM/COUNT require:

Interview questions

1. Explain the GROUP BY and HAVING clauses in Open SQL. Provide an example that counts orders by customer and filters for customers with > 10 orders.

Answer it in your own words first, then compare.

2. Explain aggregate functions in Open SQL (SUM, COUNT, AVG, MAX, MIN) and how they work with GROUP BY. Include how to handle NULL values.

Answer it in your own words first, then compare.

Practice more with Interview AI →

Reference-verified: the steps are checked against official SAP documentation and tutorials, and object and field names in the sample code are checked for consistency. The code has not been compiled or run on an SAP system by ERPClimb. Syntax marked release-dependent varies by SAP release, so check it in your own system.