Skip to Content
Author's profile photo Horst Keller

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 wacarrid.

       … wacarrid ...

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.

Assigned tags

      21 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Rüdiger Plantiko
      Rüdiger Plantiko

      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

      Author's profile photo Former Member
      Former Member

      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 syntSyntax_errors.jpgax errors.

      Thanks

      Bruce

      Author's profile photo Sandra Rossi
      Sandra Rossi

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

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      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



      Author's profile photo Abdul Hakim
      Abdul Hakim

      Thanks for the great explanation.

      Author's profile photo Former Member
      Former Member

      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 😉

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      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.

      Author's profile photo Steve Mo
      Steve Mo

      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

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

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

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

      OK for money 😉 )

      Author's profile photo Ioan Radulescu
      Ioan Radulescu

      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 .

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

       

      Wow, nice finding!

      I experience the same in our latest development system.

      I report it to development.

      Thanks for notifying.

      Horst

      Author's profile photo Former Member
      Former Member

      Hi Horst,

       

      Thank you for the rapid reply.

      Waiting for your good news. ?

       

      Regards,

      Robin Sun

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

       

      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.
      

       

       

      Author's profile photo Former Member
      Former Member

      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

       

      Author's profile photo Jacques Nomssi
      Jacques Nomssi

      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

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      The limitation is documented:

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

      Author's profile photo Amy King
      Amy King

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

      Author's profile photo Omeralp Pirim
      Omeralp Pirim

      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

      Author's profile photo SUPRIYA RAI
      SUPRIYA RAI

      Thanks Horst, Very Well Explained !!

       

      Regards,

      Supriya RC