Horst Keller

June 23, 2016 3 minute read

GROUP BY for Internal Tables – Step by Step

In my blog ABAP News for 7.40, SP08 – GROUP BY for Interna… | SCN I introduced the GROUP BY addition for LOOP AT for internal tables.

Since there seems to be some confusion about the syntax and its meaning, let us approach the grouping of internal tables step by step using a very simple case and you will see that it isn’t that complicated at all. I also attached the full text of a program, that executes the following steps and produces some output.

Say, you have an internal table spfli_tab TYPE TABLE OF spfli filled with data from database table SPFLI (what else …).

Step 1, Grouping by one column

The most simple thing you can do is grouping by one column:

LOOP AT spfli_tab INTO wa

GROUP BY wa–carrid.

… wa–carrid ...

ENDLOOP.

Inside the loop you can access wa, especially wa-carrid. wa contains the first line of each group that is created by GROUP BY. This is called representative binding. Inside the loop wa represents a group.

In order to access the members of each group, you add a member loop:

LOOP AT spfli_tab INTO wa

GROUP BY wa-carrid.

…

LOOP AT GROUP wa INTO DATA(member).

… member-… …

ENDLOOP.

…

ENDLOOP.

member is a structure with the line type of spfli_tab and contains the members of each group.

Step 2, Grouping by more than one column

If you want to group by more than one grouping criterion (columns in the most simple case), you write:

LOOP AT spfli_tab INTO wa

GROUP BY ( key1 = wa-carrid key2 = wa-airpfrom ).

… wa-carrid … wa-airpfrom …

ENDLOOP.

In this case, you construct a structured group key. In the case of representative binding shown here, wa is reused for accessing the group key inside the loop.

In order to access the members of the groups, you can add exactly the same member loop as in step 1 above.

Step 3, Group key binding for grouping by one column

Besides the representative binding, where the INTO-clause of LOOP AT is reused for accessing the group, you can define a group key binding:

LOOP AT spfli_tab INTO wa

GROUP BY wa-carrid

INTO DATA(key).

… key …

ENDLOOP.

Not too different from step 1 above, eh? Instead of reusing wa, you use an elementary data object key that for convenience is declared inline here. Group key binding offers some more functionality compared to representative binding (additions WITHOUT MEMBERS, GROUP SIZE, GROUP INDEX). If you don’t need those, you can stay with representative binding. Nevertheless, I myself always prefer group key binding, since it makes the group key more explicit. But that’s a question of taste.

Inserting the member loop works as before:

LOOP AT spfli_tab INTO wa

GROUP BY WA-CARRID

INTO DATA(key).

…

LOOP AT GROUP key INTO member.

… members …

ENDLOOP.

…

ENDLOOP.

Note, that you access the group using the name key now.

Step 4, Group key binding for grouping by more than one column

Last but not least, group key binding for structured group keys:

LOOP AT spfli_tab INTO wa

GROUP BY ( key1 = wa-carrid key2 = wa-airpfrom )

INTO DATA(key).

… key-key1 … key-key2 …

ENDLOOP.

Now, key is a structure with the components key1 and key2. The member loop can be added exactly as in step 3.

If you aren’t interested in the members, you can use the NO MEMBERS addition in order to save some run time and some memory. E.g.

LOOP AT spfli_tab INTO wa

GROUP BY ( key1 = wa-carrid key2 = wa-airpfrom

index = GROUP INDEX size = GROUP SIZE )

WITHOUT MEMBERS

INTO DATA(key).

… key-key1 … key-key2 … key-index … key-size …

ENDLOOP.

A member loop is not possible here. But key is enriched with some predefiined optional components for additional informations.

Conclusion

Not too complicated, or?

Having understood these simple facts, you can go on, read the documentation, and do more complicated things, e.g. exploiting the fact that you can use expressions for the RHS of the group key definitions or using FOR expressions instead of LOOP AT.

21 Comments

You must be Logged on to comment or reply to a post.

Rüdiger Plantiko

June 23, 2016 at 8:42 am

Thanks for the clarifications, Horst!

When reviewing my example in "SELECT DISTINCT" for internal tables - contest... | SCN,

I see that I could rewrite the GROUP BY approach further. Here is my GROUP BY proposal from that SCN message:

form with_grouping using it_vbak type vbak_tab

changing ct_ernam type ernam_tab.

clear ct_ernam.

loop at it_vbak assigning field-symbol(<ls_vbak>)

group by <ls_vbak>-ernam

ascending

assigning field-symbol(<lv_group>).

append <lv_group> to ct_ernam.

endloop.

endform.

Firstly, I could switch off group member binding (which, as the docu says, could improve performance, for the price of not being able to perform an inner LOOP AT GROUP or to access <ls_vbak> inside of the loop). Apart from performance, it clarifies my intent that I am only interested in the group key here, not in the table entries themselves:

loop at it_vbak assigning field-symbol(<ls_vbak>)

group by <ls_vbak>-ernam

without members

assigning field-symbol(<lv_group>).

append <lv_group> to ct_ernam.

endloop.

If I wrote "append <ls_vbak>-ernam to ct_ernam" instead of working with <lv_group>, I get no syntax error (or even a warning) in my release (Kernel 742, SAP_BASIS 740.0008), as I would have expected, but a short dump GETWA_NOT_ASSIGNED in execution.

Alternatively, I could simply write

loop at it_vbak assigning field-symbol(<ls_vbak>)

group by <ls_vbak>-ernam.

append <ls_vbak>-ernam to ct_ernam.

endloop.

This is clearly shorter, similar in size to the old-style COLLECT approach. But inside of the loop, each first row of it_vbak with a new ERNAM shows up, although I don't need the full row, and using the other fields would be misleading, since they are randomly in a sense (this is why the were "commented out" with stars in the previous loop grouping idioms AT NEW... and AT END OF ... ).

Indeed, it would be nice to have something like

loop at it_vbak group by ernam assigning field-symbol(<lv_group>).

append <lv_group> to ct_ernam.

endloop.

(as discussed in the comment section of your former blog post), but I fully understand that this was rejected by the developers for the reason that the position after GROUP BY is a general expression, which could contain things like function calls to build the key values (and writing just the column name ERNAM breaks this concept, it is no valid expression).

Regards,

Rüdiger

Former Member

June 23, 2016 at 5:52 pm

We are on Support Package SAPKA74007. There were many syntax errors in the zipped code attachment. Would you expect the code to work on our system? I have attached a screen print with the syntax errors.

Thanks

Bruce

Sandra Rossi

June 23, 2016 at 7:08 pm

Many changes in 7.40 are only from SAPKA74008 (refer to the ABAP release notes).

Horst Keller

Blog Post Author

June 24, 2016 at 5:59 am

In the first sentence I refer to "ABAP News for 7.40, SP08".

Therefore, I would not expect the code to work on your system, but as Sandra said from SAPKA74008 on (and in fact it does).

To see the release notes in your system, run TA ABAPHELP and enter NEWS.

Horst

Abdul Hakim

June 23, 2016 at 9:30 pm

Thanks for the great explanation.

Former Member

June 29, 2016 at 7:12 am

Thanks Horst! Now i guess that i can use the GROUP BY construct without hitting the F1 key 😛

I wish this lucid explanation were part of ABAP Keyword documentation 😉

Horst Keller

Blog Post Author

July 1, 2016 at 8:55 am

Your wish is my command ... 😉

But as you see from the attached program's name, I already created it for that purpose in the ABAP Example Library in package SABAPDEMOS and of course the code and the description will be part of the documentation of the next release.

Steve Mo

July 10, 2016 at 7:58 pm

Thanks for more clear explanation, Horst.

One question: Does GROUP BY support dynamic programming? Such as GROUP BY <wa>-(lv_dynamic). I tried but syntax error exists said dynamic attribute is not allowed.

Is that possible with some other ways?

BR,
Steve

Horst Keller

Blog Post Author

July 10, 2016 at 8:04 pm

No, the documentation does not mention dynamic GROUP BY, does it?

(Sometimes I wonder why I write all that stuff ...,

OK for money 😉 )

Ioan Radulescu

March 1, 2017 at 8:11 pm

many of the links you have in your blogs (which I enjoy reading lately) point eventually at http://help-legacy.sap.com/abapdocu_750/en/index.htm .

Horst Keller

Blog Post Author

March 2, 2017 at 6:48 am

There’s been a migration, see my

Blog.

Redirects should work but they don’t. Adjusting all links by hand? Maybe a ticket from customer side to SAP can help.

Former Member

May 9, 2017 at 5:45 am

Hi Horst,

Great blog indeed! This is really an easy to be understood way.

When I was trying this piece of code, it works fine very well.

CLASS demo DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS:
      main,
      class_constructor.
  PRIVATE SECTION.
    CLASS-DATA
          itab TYPE TABLE OF i WITH EMPTY KEY.
ENDCLASS.

CLASS demo IMPLEMENTATION.
  METHOD main.
    DATA(out) = cl_demo_output=>new( ).

    DATA members LIKE itab.

    out->begin_section( `LE 5, BT 2 AND 7, GT 5` ).
    LOOP AT itab INTO DATA(wa)
          GROUP BY
            COND string(
              WHEN wa <= 5 THEN `LE 5`
              WHEN wa >  2 AND wa <= 7 THEN `BT 2 AND 7`
              WHEN wa >  5 THEN `GT 5` )
          ASSIGNING FIELD-SYMBOL(<group>).
      out->begin_section( <group> ).
      CLEAR members.
      LOOP AT GROUP <group> ASSIGNING FIELD-SYMBOL(<wa>).
        members = VALUE #( BASE members ( <wa> ) ).
      ENDLOOP.
      out->write( members )->end_section( ).
    ENDLOOP.

    out->next_section( `BT 2 AND 7, LE 5, GT 5` ).
    LOOP AT itab INTO wa
    GROUP BY
      COND string(
        WHEN wa >  2 AND wa <= 7 THEN `BT 2 AND 7`
        WHEN wa <= 5 THEN `LE 5`
        WHEN wa >  5 THEN `GT 5` )

      ASSIGNING <group>.
      out->begin_section( <group> ).
      CLEAR members.
      LOOP AT GROUP <group> ASSIGNING <wa>.
        members = VALUE #( BASE members ( <wa> ) ).
      ENDLOOP.
      out->write( members )->end_section( ).
    ENDLOOP.

    out->display( ).
  ENDMETHOD.
  METHOD class_constructor.
    itab = VALUE #( FOR j = 1 UNTIL j > 10 ( j ) ).
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  demo=>main( ).

But if I change a little bit for the loop at group like this below

CLASS demo DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS:
      main,
      class_constructor.
  PRIVATE SECTION.
    CLASS-DATA
          itab TYPE TABLE OF i WITH EMPTY KEY.
ENDCLASS.

CLASS demo IMPLEMENTATION.
  METHOD main.
    DATA(out) = cl_demo_output=>new( ).

    DATA members LIKE itab.

    out->begin_section( `LE 5, BT 2 AND 7, GT 5` ).
    LOOP AT itab INTO DATA(wa)
          GROUP BY (
            key = COND string(
                    WHEN wa <= 5 THEN `LE 5`
                    WHEN wa >  2 AND wa <= 7 THEN `BT 2 AND 7`
                    WHEN wa >  5 THEN `GT 5` )
            index = GROUP INDEX
            size  = GROUP SIZE
          )
          ASSIGNING FIELD-SYMBOL(<group>).
      out->begin_section( <group>-key ).
      CLEAR members.
      LOOP AT GROUP <group> ASSIGNING FIELD-SYMBOL(<wa>).
        members = VALUE #( BASE members ( <wa> ) ).
      ENDLOOP.
      out->write( members )->end_section( ).
    ENDLOOP.

    out->next_section( `BT 2 AND 7, LE 5, GT 5` ).
    LOOP AT itab INTO wa
    GROUP BY
      COND string(
        WHEN wa >  2 AND wa <= 7 THEN `BT 2 AND 7`
        WHEN wa <= 5 THEN `LE 5`
        WHEN wa >  5 THEN `GT 5` )

      ASSIGNING <group>.
      out->begin_section( <group> ).
      CLEAR members.
      LOOP AT GROUP <group> ASSIGNING <wa>.
        members = VALUE #( BASE members ( <wa> ) ).
      ENDLOOP.
      out->write( members )->end_section( ).
    ENDLOOP.

    out->display( ).
  ENDMETHOD.
  METHOD class_constructor.
    itab = VALUE #( FOR j = 1 UNTIL j > 10 ( j ) ).
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  demo=>main( ).

I just couldn't activate this one. The editor is just hanging there without any error message or warning ones. I am wondering if there are something wrong with it?

Thank you in advance for your feedback.

Regards,

Robin Sun

Horst Keller

Blog Post Author

May 9, 2017 at 5:51 am

Wow, nice finding!

I experience the same in our latest development system.

I report it to development.

Thanks for notifying.

Horst

Former Member

May 9, 2017 at 8:22 am

Hi Horst,

Thank you for the rapid reply.

Waiting for your good news. ?

Regards,

Robin Sun

Horst Keller

Blog Post Author

May 9, 2017 at 8:45 am

Indeed, there is an error in the compiler that is corrected now in the current release (due to your notification).

The good news is, it is not urgent.

There is a syntax error in your program that leads to the endless loop. You reuse <group> with a wrong type.

Change the last part of your code to the following and it compiles.

    out->next_section( `BT 2 AND 7, LE 5, GT 5` ).
    LOOP AT itab INTO wa
    GROUP BY
      COND string(
        WHEN wa >  2 AND wa <= 7 THEN `BT 2 AND 7`
        WHEN wa <= 5 THEN `LE 5`
        WHEN wa >  5 THEN `GT 5` )
      ASSIGNING FIELD-SYMBOL(<groupi>).
      out->begin_section( <groupi> ).
      CLEAR members.
      LOOP AT GROUP <groupi> ASSIGNING <wa>.
        members = VALUE #( BASE members ( <wa> ) ).
      ENDLOOP.
      out->write( members )->end_section( ).
    ENDLOOP.

Former Member

May 9, 2017 at 8:59 am

Hi Horst,

Nice correction, and now I will remeber for each loop, it would be better to use a new key variant.

Thank you again for this fanstatic series of blogs because via these simply words to introduce the new features, I could have this improvement today, and if I get it everyday, I believe it would be a dramatic leap finally.

Best regards,

Robin Sun

Jacques Nomssi

August 12, 2017 at 1:50 pm

Hello Horst,

I could only change data in a grouped loop when WITHOUT MEMBERS was used:

    LOOP AT ct_edidc INTO DATA(ls_edidc)
      GROUP BY ( idoctp = ls_edidc-idoctp
                 cimtyp = ls_edidc-cimtyp )
      WITHOUT MEMBERS    "<--------- syntax error if removed
      ASSIGNING FIELD-SYMBOL(<ls_group>).

      DELETE ct_edidc WHERE idoctp = <ls_group>-idoctp
                        AND cimtyp = <ls_group>-cimtyp.
    ENDLOOP.

Changing data in a loop without grouping is possible. What are the limitations in the case of grouped loops?

best regards,

JNN

Horst Keller

Blog Post Author

August 15, 2017 at 6:10 am

The limitation is documented:

The internal table itab cannot be modified in the group loop unless the addition WITHOUT MEMBERS is specified.

Amy King

April 18, 2018 at 11:19 am

Thanks Horst. Another clear tutorial on 740's many new ABAP language features!

Omeralp Pirim

July 6, 2018 at 9:10 am

Hello Horst,

I have a question about dynamic table in loop at group. How can we use dynamic group condition ?

I wrote something like that but it is not working.

    LOOP AT <fs_table> ASSIGNING FIELD-SYMBOL(<fs_line>)
    GROUP BY  COND string( WHEN r_b1 = abap_true  THEN  'SATNR = <FS_LINE>-SATNR'
                          WHEN r_b2 = abap_true THEN lv_group &&  ' BRAND_ID = <FS_LINE>-BRAND_ID MATKL = <FS_LINE>-MATKL'
                          WHEN r_b3 = abap_true THEN lv_group &&  ' BRAND_ID = <FS_LINE>-BRAND_ID WGHIE = <FS_LINE>-WGHIE'
                          WHEN r_b4 = abap_true THEN lv_group &&  ' WGHIE = <FS_LINE>-WGHIE'
                          ELSE   lv_group )
    ASSIGNING FIELD-SYMBOL(<group>) .
      DATA(lv_sum_kbetr) = VALUE kbetr( ).
      LOOP AT GROUP <group> ASSIGNING FIELD-SYMBOL(<fs_sgroup>).
        ls_line = CORRESPONDING #( <fs_sgroup> ).
        lt_copy_staffel = VALUE #( BASE lt_copy_staffel (  kopos  = '01'
                                                           klfn1  = sy-tabix
                                                           kstbm  = ls_line-kstbm
                                                           kbetr  = ls_line-kbetr
                                                           kzbzg  = 'G'
                                                           rv13akonwa = ls_line-waers
*                                                           konpkmein  = 'ST'
*                                                           konpkonms  = '%'
                                                            )  ).
*        members = VALUE #( BASE members ( <flight> ) ).
        lv_sum_kbetr = lv_sum_kbetr + ls_line-kbetr.
      ENDLOOP.
      lt_copy_records = VALUE #( ( mandt = sy-mandt
                                   kopos  = '01'
                                   knumh = '$000000001'
                                   kappl = 'M'
                                   kschl = 'ZPM1'
                                   krech = 'H'
                                   kbetr = lv_sum_kbetr
                                   waers = '%'
                                   konms = '%'
                                   kzbzg = 'G'
                                   stfkz = 'A'
                                   updkz = 'I' ) ).
      DATA(key_fields) =  CORRESPONDING komg( ls_line MAPPING matnr = satnr ).
      lt_copy_recs_idoc = VALUE #( ( kznep = abap_true ) ).
      ASSIGN COMPONENT 'STATU' OF STRUCTURE <fs_sgroup> TO FIELD-SYMBOL(<statu>).
      ASSIGN COMPONENT 'MESSAGE' OF STRUCTURE <fs_sgroup> TO FIELD-SYMBOL(<message>).
      CALL FUNCTION 'RV_CONDITION_COPY'
        EXPORTING
          application                 = 'M'    " Application
          condition_table             = COND kotabnr( WHEN r_b1 = abap_true THEN '919'
                                                WHEN r_b2 = abap_true THEN '920'
                                                WHEN r_b3 = abap_true THEN '921'
                                                WHEN r_b4 = abap_true THEN '922'
                                                WHEN r_b4 = abap_true THEN '923'
                                                )    " Condition Table Number
          condition_type              = 'ZPM1'
          date_from                   = ls_line-datab    " Valid-from date
          date_to                     = ls_line-datbi     " Valid-to Date
          enqueue                     = abap_true    " Lock indicators of condition recor
          key_fields                  = key_fields    " Key fields
        TABLES
          copy_records                = lt_copy_records    " Table of conditions to be copied
          copy_staffel                = lt_copy_staffel
          copy_recs_idoc              = lt_copy_recs_idoc
        EXCEPTIONS
          enqueue_on_record           = 1
          invalid_application         = 2
          invalid_condition_number    = 3
          invalid_condition_type      = 4
          no_authority_ekorg          = 5
          no_authority_kschl          = 6
          no_authority_vkorg          = 7
          no_selection                = 8
          table_not_valid             = 9
          no_material_for_settlement  = 10
          no_unit_for_period_cond     = 11
          no_unit_reference_magnitude = 12
          invalid_condition_table     = 13.
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                   WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO DATA(lv_message).
        <statu> = space.
        <message> = lv_message.
      ELSE.
        CLEAR knumh_map[].
        CALL FUNCTION 'RV_CONDITION_SAVE'
          TABLES
            knumh_map = knumh_map.

        CALL FUNCTION 'RV_CONDITION_RESET'.
        COMMIT WORK AND WAIT.
        <statu> = abap_true.
        TRY.
            <message> = knumh_map[ 1 ]-knumh_new && '-Başarılı'(s01).
          CATCH cx_sy_itab_line_not_found.
            <message> =  '-Başarılı'(s01).
        ENDTRY.
      ENDIF.
      MODIFY  <fs_table> FROM <fs_sgroup> TRANSPORTING ('STATU') ('MESSAGE') WHERE (lv_where_m) .
      IF sy-subrc = 0.
        rt_bool = abap_true.
        MODIFY (tabname) FROM TABLE <fs_table>.
        IF  sy-subrc = 0.
          COMMIT WORK AND WAIT.
        ELSE.
          ROLLBACK WORK.
          MESSAGE 'Veri tababanına yazarken hata!'(e01) TYPE 'I' DISPLAY LIKE 'E'.
        ENDIF.
      ELSE.
        rt_bool = space.
      ENDIF.
    ENDLOOP.

Regards

OAP

SUPRIYA RAI

June 10, 2019 at 9:10 am

Thanks Horst, Very Well Explained !!

Regards,

Supriya RC

GROUP BY for Internal Tables – Step by Step

Step 1, Grouping by one column

Step 2, Grouping by more than one column

Step 3, Group key binding for grouping by one column

Step 4, Group key binding for grouping by more than one column

Conclusion

Assigned Tags