SUM totals by FOR ALL ENTRIES itab keys

Question

I want to execute a SELECT query on a database table that has 6 key fields, let's assume they are keyA, keyB, ..., keyF.

As input parameters to my ABAP function module I do receive an internal table with exactly that structure of the key fields, each entry in that internal table therefore corresponds to one tuple in the database table.

Thus I simply need to select all tuples from the database table that correspond to the entries in my internal table. Furthermore, I want to aggregate an amount column in that database table in exactly the same query.

In pseudo SQL the query would look as follows: SELECT SUM(amount) FROM table WHERE (keyA, keyB, keyC, keyD, keyE, keyF) IN {internal table}.

However, this representation is not possible in ABAP OpenSQL.

Only one column (such as keyA) is allowed to state, not a composite key. Furthermore I can only use 'selection tables' (those with SIGN, OPTIOn, LOW, HIGH) after they keyword IN. Using FOR ALL ENTRIES seems feasible, however in this case I cannot use SUM since aggregation is not allowed in the same query.

Any suggestions?

score 3 · Answer 1 · answered Apr 15 '15 at 10:07

For selecting records for each entry of an internal table, normally the for all entries idiom in ABAP Open SQL is your friend. In your case, you have the additional requirement to aggregate a sum. Unfortunately, the result set of a SELECT statement that works with for all entries is not allowed to use aggregate functions. In my eyes, the best way in this case is to compute the sum from the result set in the ABAP layer. The following example works in my system (note in passing: using the new ABAP language features that came with 7.40, you could considerably shorten the whole code).

report  zz_ztmp_test.

start-of-selection.
  perform test.

* Database table ZTMP_TEST :
* ID     -  key field  - type CHAR10
* VALUE  -  no key field - type INT4
* Content: 'A' 10, 'B' 20, 'C' 30, 'D' 40, 'E' 50

types: ty_entries type standard table of ztmp_test.

* ---
form test.

  data: lv_sum    type i,
        lt_result type ty_entries,
        lt_keys   type ty_entries.

  perform fill_keys changing lt_keys.

  if lt_keys is not initial.
    select * into table lt_result
           from ztmp_test
           for all entries in lt_keys
           where id = lt_keys-id.
  endif.

  perform get_sum using lt_result
                  changing lv_sum.

  write: / lv_sum.

endform.

form fill_keys changing ct_keys type ty_entries.
  append :
    'A' to ct_keys,
    'C' to ct_keys,
    'E' to ct_keys.
endform.

form get_sum using it_entries type ty_entries
              changing value(ev_sum) type i.
  field-symbols: <ls_test> type ztmp_test.

  clear ev_sum.
  loop at it_entries assigning <ls_test>.
    add <ls_test>-value to ev_sum.
  endloop.

endform.

I also do not see another option to compute the sum manually afterwards. Do you know the reason for the restriction not to being able to use aggregation in conjunction with FOR ALL ENTRIES? — Emdee, Apr 16 '15 at 09:19
`FOR ALL ENTRIES` has an own mechanism of aggregation - it creates an internal table with all the result fields as components, and condenses identical lines, so it makes an intrinsic `DISTINCT` for the result set. This is one of the caveas of `FOR ALL ENTRIES`. This built-inn `DISTINCT`logic can't be combined with other aggregate functions. In short: It's not implemented :-) — rplantiko, Apr 16 '15 at 12:58

Smigs · Answer 2 · 2016-05-31T15:28:30.717

2

I would use FOR ALL ENTRIES to fetch all the related rows, then LOOP round the resulting table and add up the relevant field into a total. If you have ABAP 740 or later, you can use REDUCE operator to avoid having to loop round the table manually:

DATA(total) = REDUCE i( INIT sum = 0
                        FOR wa IN itab NEXT sum = sum + wa-field ).

edited May 31 '16 at 15:28

answered Apr 15 '15 at 10:21

Smigs

2,362
1
21
24

Thanks for sharing this nice and smooth way to express a loop – Emdee Apr 16 '15 at 09:21

Suncatcher · Answer 3 · 2020-03-20T14:20:19.677

One possible approach is simultaneous summarizing inside SELECT loop using statement SELECT...ENDSELECT statement.

Sample with calculating all order lines/quantities for the plant:

TYPES: BEGIN OF ls_collect,
       werks TYPE t001w-werks,
       menge TYPE ekpo-menge,
      END OF ls_collect.
DATA: lt_collect TYPE TABLE OF ls_collect.

SELECT werks UP TO 100 ROWS
  FROM t001w
  INTO TABLE @DATA(lt_werks).

SELECT werks, menge
  FROM ekpo
  INTO @DATA(order)
   FOR ALL ENTRIES IN @lt_werks
  WHERE werks = @lt_werks-werks.

  COLLECT order INTO lt_collect.
ENDSELECT.

The sample has no business sense and placed here just for educational purpose.

Another more robust and modern approach is CTE (Common Table Expressions) available since ABAP 751 version. This technique is specially intended among others for total/subtotal tasks:

WITH
  +plants AS (
    SELECT werks UP TO 100 ROWS
      FROM t011w ),
  +orders_by_plant AS (
     SELECT SUM( menge )
      FROM ekpo AS e
      INNER JOIN +plants AS m
         ON e~werks = m~werks 
      GROUP BY werks )

    SELECT werks, menge
       FROM +orders_by_plant
       INTO TABLE @DATA(lt_sums)
       ORDER BY werks.

cl_demo_output=>display( lt_sums ).

The first table expression +material is your internal table, the second +orders_by_mat quantities totals selected by the above materials and the last query is the final output query.

SUM totals by FOR ALL ENTRIES itab keys

3 Answers3