Skip to Content
Author's profile photo Horst Keller

ABAP News for 7.40, SP08 – GROUP BY for Internal Tables

You know the GROUP BY clause from SQL. There was not such a clause for internal tables up to now. All we had was that clumsy group level processing with statements AT NEW … that relied on the order of table columns and contents that is sorted respectively.

With release 7.40, SP08 there is a real GROUP BY clause for LOOP AT itab that is much more powerful than the SQL one.

DATA flights TYPE TABLE OF spfli WITH EMPTY KEY.

SELECT * FROM  spfli
         WHERE carrid = ‘…’
         INTO TABLE @flights.

DATA members LIKE flights.
LOOP AT flights INTO DATA(flight)
     GROUP BY ( carrier = flight-carrid cityfr = flight-cityfrom )
              ASCENDING
              ASSIGNING FIELD-SYMBOL(<group>).
  CLEAR members.
  LOOP AT GROUP <group> ASSIGNING FIELD-SYMBOL(<flight>).
    members = VALUE #( BASE members ( <flight> ) ).
  ENDLOOP.
  cl_demo_output=>write( members ).
ENDLOOP.
cl_demo_output=>display( ).

Looks like dreaded nested LOOPs, but it isn’t quite that – no quadratic behavior!  What happens here is that the first LOOP statement is executed over all internal table lines in one go and the new GROUP BY addition groups the lines. Technically, the lines are bound internally  to a group that belongs to a group key that is specified behind GROUP BY.The group key is calculated for each loop pass. And the best is, it need not be as simple as using only column values, but you can use any expressions here that normally depend on the contents of the current line, e.g. comparisons, method calls, …. The LOOP body is not evaluated in this phase!

Only after the grouping phase, the LOOP body is evaluated. Now a second (but not nested) loop is carried out over the groups constructed in the first phase. Inside this group loop you can access the group using e.g. the field symbol <group> that is assigned to the group in the above example. If you want to access the members of the group, you can us the new LOOP AT GROUP statement, which enables a member loop within the group loop. In the example, the members are inserted into a member table and displayed.

Here another example, where the group key is evaluated from method calls:

LOOP AT flights INTO DATA(wa)

     GROUP BY ( tz_from = get_time_zone( wa-airpfrom )

                tz_to   = get_time_zone( wa-airpto ) )

     ASSIGNING FIELD-SYMBOL(<group>).

  …

ENDLOOP.

Of course, there is also expression enabled syntax for grouping internal tables.

In a first step, we get rid of LOOP AT GROUP by replacing it with a FOR expression:

DATA members LIKE flights.

LOOP AT flights INTO DATA(flight)

     GROUP BY ( carrier = flight-carrid cityfr = flight-cityfrom )

              ASCENDING

              ASSIGNING FIELD-SYMBOL(<group>).

  members = VALUE #( FOR m IN GROUP <group> ( m ) ).

  cl_demo_output=>write( members ).

ENDLOOP.

cl_demo_output=>display( ).

The IN GROUP is a new addition to FOR. Second, away with the outer LOOP:

TYPES t_flights LIKE flights.

DATA out TYPE REF TO if_demo_output.

out = REDUCE #( INIT o = cl_demo_output=>new( )

                FOR GROUPS <group> OF flight IN flights

                GROUP BY ( carrier = flight-carrid cityfr = flight-cityfrom )

                  ASCENDING

                LET members = VALUE t_flights( FOR m IN GROUP <group> ( m ) ) IN

                NEXT o = o->write( members ) ).

out->display( ).

FOR GROUPS is another new FOR variant. Believe me, it does the same as the variants above. But for reasons of readability, the combination of LOOP AT GROUP with a FOR IN GROUP within might be the preferable one, at least for this example 😈 .

For a deep dive, read this and this!

PS: If you are confused what kind of loops and nesting takes place, have a look at my comment below.

PPS: For a step-by-step approach to GROUP BY see GROUP BY for Internal Tables – Step by Step

Assigned Tags

      62 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Once again, very interesting.

      1. Why actually do we have to define the group_result ({INTO group}|{ASSIGNING <group>} in case we are only interested of his members?

      i.e.

      Why the syntax is "LOOP AT GROUP <group>"

      instead of

      "LOOP AT GROUP" (or "LOOP AT GROUP group_name")

      ?

      2. Regarding last example,

      The monsters, oh the monsters...

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

      1. You can nest all these LOOPs and FORs.  Inside a nested loop there might be several groupings available. Therefore, you must explicitly specify the groups you want to iterate.

      2. 😀

      Author's profile photo Daniel Ruiz
      Daniel Ruiz

      Hi Horst,

      first of all, I'm not an ABAP developer.. but, nonetheless, I need to deal with ABAP because I ended up in a company that works with SAP.. but I'm a big fan of your posts because you bring new stuff to the table.

      my question to you is: why not just a for or foreach like other languages?

      as far I understood, the group by (key) is a full iteration over the list performing a simple comparison statement to add or not something into a group?

      what I mean is, it seemed to me ABAP was moving to a less statement / keyword language, but I can only assume folks at SAP cannot do it because ABAP itself is darn slow to cope with high processing, hence introductions of new keywords and statement to run 'simple loops' in a C based kernel which effectively, will provide some speed.

      it's rather pointless to have 300 keywords to do simple logic, because instead of executing the simple logic in a code developed in ABAP we run it in the kernel using C.. wouldn't it be best to just expose a C API, this way when we need some extra speed we just code in C directly?

      also, I understand a internal_table in ABAP may not necessarily come from a query, but I did have the impression you mentioned the 'kernel' group by is better (and I can only assume you are comparing performance) than a group by on the database layer.. how is that even remotely possible?

      []'s

      D.

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

      why not just a for or foreach like other languages?

      There is a general FOR ...

      the group by (key) is a full iteration over the list performing a simple comparison statement to add or not something into a group?

      Yes, but the full iteration can be limited by a WHERE condition. The "simple comparison" statement" can be any expression, including method calls.

      ABAP itself is darn slow to cope with high processing, hence introductions of new keywords and statement to run 'simple loops' in a C based kernel which effectively, will provide some speed.

      Hmm, not really. In fact ABAP can be astonishingly fast, once you have the data on the application server and if you do it right. The new GROUP BY can more be seen as a convenience tool that relieves you from programming the temporary internal group table for yourself. The simple fast loops are already available since long.

      it's rather pointless to have 300 keywords to do simple logic, because instead of executing the simple logic in a code developed in ABAP we run it in the kernel using C.. wouldn't it be best to just expose a C API, this way when we need some extra speed we just code in C directly?

      Your'e questioning the existence of ABAP as a 4GL on its application server as a whole. You might also ask, why do we need SQL, if I can access the contents of dabase tables with C directly? Or why do we need database tables at all, having a fprintf in C. Such a discussion should be started separately. But please be aware that a concept like internal tables is not readily available in C and we are not talking about simple C FORs here.

      the 'kernel' group by is better (and I can only assume you are comparing performance) than a group by on the database layer..

      I said it is more powerful than the GROUP BY of SELECT. While SELECT allows you to group by contents of database columns only, the GROUP BY for internal tables can group by arbitrary expressions.

      Author's profile photo Daniel Ruiz
      Daniel Ruiz

      There is a general FOR ...

      fairly positive this is not what I meant.. but I like the fact at least inside an expression we can use a for.

      Yes, but the full iteration can be limited by a WHERE condition. The "simple comparison" statement" can be any expression, including method calls.

      a comparison is a comparison, they are usually simple and most cases they are not simple is due to problematic code (99% of the cases I saw so far) - would love to understand what happens behind the curtains; "where" is much like a filter applied most probably the same way as group?

      Hmm, not really. In fact ABAP can be astonishingly fast, once you have the data on the application server and if you do it right. The new GROUP BY can more be seen as a convenience tool that relieves you from programming the temporary internal group table for yourself. The simple fast loops are already available since long.

      I totally understand, and this is exactly what I question: if I do a loop, some if's internally and end up with another 'internal table' (the group version) - I suppose the performance will be the same right? - somehow I feel that using the 'group' will be a lot faster.. and yet, another 'keyword' added for a simple for + ifs.

      Your'e questioning the existence of ABAP as a 4GL on its application server as a whole. You might also ask, why do we need SQL, if I can access the contents of dabase tables with C directly? Or why do we need database tables at all, having a fprintf in C. Such a discussion should be started separately. But please be aware that a concept like internal tables is not readily available in C and we are not talking about simple C FORs here.

      not really questioning the existence: I understand why ABAP still exists, and we need SQL because SQL works pretty well - we also need the database layer in there, however its rather tricky when you have to explain that a " loop. loop. endloop. endloop." is not 'really' a nested loop.. that IS a nested loop, unless my eyes are playing tricks on me.. - now I honestly believe this is about performance, but you don't think this is about performance, so we move the page.

      Author's profile photo Kilian Kilger
      Kilian Kilger

      Hi Daniel,

      it is a nested loop by "syntax". But it is not really in nested loop in ABAP because the first loop which really does the grouping work is done in the kernel. So if you only count what happens in the ABAP, the performance is linear and not quadratic. This is what Horst meant with "not nested". Of course, you can't trick the theoretical complexity here. So actually we have 3 loops here: the first loop, which does all the work in the kernel. The outer loop over the grouping sets in ABAP and the inner loop over the grouping members. The last two loops which you see in the ABAP are (together!) only linear in the size of the resulting internal table.

      You should compare the GROUP BY (and all constructor expressions) not to ancient C constructs like for(;;) or even foreach(...) but to much more powerful constructs coming from functional programming languages like Haskell (e.g. list comprehension). You will see _much_ more complex expressions in your "comparisons" in your favourite language too in the future. Just look what is possible in C++ or Java nowadays with lambda's. I guess that not all lambda functions contain only "simple comparisons" ;-).

      Best regards,

      Kilian.

      Author's profile photo Daniel Ruiz
      Daniel Ruiz

      Hi Killian,

      pretty positive I won't see anything like this, and I still don't understand what you mean by complex expressions... doing a simple map with a key based on comparisons carrying a list inside is something pretty basic.

      even list comprehension, as you mentioned, can be easily achieved in OO, so I still cannot find any other good argument other than speed to have such in the kernel "with a nested loop by syntax" (God knows what you mean by that)  - instead of doing the same in a OO fashion..

      there are no real favourites in my list, there are the good ones and the bad ones..

      Cheers,

      D.

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

      can be easily achieved in OO, so I still cannot find any other good argument other than speed

      When teaching ABAP and ABAP Objects to Java programmers you are often asked, why do you have concepts like internal tables at all? Why don't you offer an OO framework that does the same? The answer is, because ABAP is ABAP where traditionally statements and type concepts play the role that OO programmed frameworks do in lower level languages. It would be nonsense to introduce system classes in ABAP that replace the existing internal table framework (and yes of course in this case also for performance reasons). ABAP Objects offers the capabilities of OO in ABAP but is not there to replace established ABAP concepts. We tried to explain this also here: http://help.sap.com/abapdocu_740/en/index.htm?file=abenobj_oriented_guidl.htm.

      Besides performance there is also the convenience argument. Already the old internal table grouping implemented by the AT NEW ... commands was purely convenience. You could achieve the same with IF ... ENDIF but ABAP offered simply shortcuts for that involving some preconditions regarding the contents of the internal tables.

      The new GROUP BY can be seen in the same tradition. It relieves you from programming the grouping yourself and by the possiblity of using arbitrary expressions for the grouping condition it is very powerful and reusable for all kinds of tasks. In an OO framework I guess you would have to offer a redefinable method that defines the grouping criteria and I think it would involve a good deal of RTTS to create and handle the internal reference table that is created under the hood of the GROUP BY addition. Creating (not filling) the internal artifacts might in fact be faster in the kernel compared to doing it with ABAP means. But that should be negligible compared to the processing of the loops itself and I don't expect that the GROUP BY - LOOP AT GROUP are faster than a selfprogrammed ABAP framework since in the end the same kernel modules are called.

      So from my point of view, the answer to your question is:

      -> It lies in the foundation of the ABAP language to offer functionality in the form of statements or more recently in the form of  built-in expressions where other languages use OO frameworks. It is often for performance reasons, but not always. (In fact there are even some ABAP statements that are implemented at least partly in ABAP itself).

      Author's profile photo Daniel Ruiz
      Daniel Ruiz

      hi Horst,

      more likely you're not teaching anything to a Java person - probably, just trying to explain the foundations of something that makes not a lot of sense by all other standards around the globe.. - remember: the Java person usually saw 'a lot of stuff', more likely to tell an ABAPer what work and what doesn't work and not the opposite as you may think.

      🙂

      now, internal tables.. good question, and as far I can understand by reading own SAP code, it's because SAP itself cannot get rid of it.. ' ABAP is ABAP ' is a rather 'lame' (no offense here, I just don't know a better word to use) excuse, anyone with very little knowledge will understand ABAP is a procedural language and somehow, probably due a lot of pressure, it has introduced some OO elements to shut the hippies up.. of course, since it's not a good OO implementation, more likely the recommendations "don't even try something fancy because you will fail miserably" are pretty much the recommendations (or difference) between doing OO in Java / .NET / C++ and ABAP.

      and as I mentioned before, I understand performance argument: the convenience argument on the other hand, it's rather difficult for me to grasp.. ABAP has far too many keywords to do rather simple basic tasks, in OO you can easily achieve with some interface and some inline class declaration - C++/Java/.NET -- while of course, not in ABAP "OO".

      so till here, all understood: ABAP has no real power when working with Objects and no real Collections framework, the GROUP BY it's about performance and convenience (of remembering another way of arranging keywords instead of just applying some logic; the very same logic someone has written in C layer) which I cannot understand, but one question still remains:

      - why not a native interface? - that would more likely to solve the performance issue in future right?

      Cheers,

      D.

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      Hi Daniel,

      I've long fought with myself whether I should enter this, lets call it "discussion". I don't know why you still work with ABAP, if you don't like the language. Language wars never have and never will come to a conclusion.

      "ABAP is ABAP" is as lame as "JAVA is JAVA" or "C++ is C++", but some kind of true if you know the history of these languages and the reason why they are made in the way they are made.

      ABAP was build as language for high robust business applications in a time where only COBOL, FORTRAN and PL/1 are used for these kind of applications. Sometimes you can still smell the taste of COBOL in the coding of some ABAP developers.

      Unfortunately (or not) SAP has to handle backward compatibility of all ABAP statements, so it's not possible to recreate the language from the scratch even if they try (and they have tried already, believe me).

      But with every evolution (not revolution) ABAP becomes better and better, especially with the last support packages (SP5 and SP8 of NW 7.40). I'm working with the language since more than 20 years now, and for the job we are doing, it's still the best solution IMHO.

      Side note: if you want to create a table/list/collection (or what ever you want to call it) of everything, you can do it even with ABAP, but you don't really want to do it:

          DATA itab TYPE STANDARD TABLE OF REF TO data.

          itab = VALUE #(

                  ( REF #( cl_demo_calculator_app=>handle ) )

                  ( REF #( 666 ) )

                  ( REF #( `a string` ) )

                 ).

      I don't think internal tables are a weakness of ABAP, quite the contrary. But as always, this is my personnel view and nobody has to be in complete agreement.

      Author's profile photo Daniel Ruiz
      Daniel Ruiz

      hi Uwe,

      ABAP is just another language I have to deal with.. it's not about 'still work with ABAP', sometimes I still have to touch it, but not all the time.. and somehow I find myself one of the 'lucky' developers who takes time to come here read, talk and try to understand motives behind the language and all the new things being developed, even thou I really don't use it much.. not too sure if we can say the same about the majority of developers out there that use ABAP every day.. many folks around me have no clue what's happening in the new SP's, and they would not understand the sample you just posted either..

      and since I'm talking about your sample, well.. it is pretty much like an Object[], which is not what's in question here... and btw, I do have my classes to deal with collections already coded... still, does not answer why there's no such classes already coded and delivered by SAP, so someone inclined to OO could use them instead of internal tables.


      IMO it seems SAP is breaking the 'ABAP' shackles lately investing a lot in other technologies and more and more people like me will show up, and they will have eventually to land their hands in ABAP..


      it seems there's too much 'emotion' from the core 'users' because they seem to have no escape... but, that's just my opinion.

      Cheers,

      D.

      Author's profile photo Kilian Kilger
      Kilian Kilger

      Hi Daniel,

      it is actually very easy to understand. The _two_ loops together just run 1 times through the internal table. So there is written two times a LOOP statement, but only one run through the internal table.

      That is very simple mathematics, you don't have to be a god to understand that ;-).

      Best regards,

      Kilian.

      Author's profile photo Daniel Ruiz
      Daniel Ruiz

      hi Kilian,

      it is a nested loop, it's what happens in the kernel even thou it will only pass once in one of them.. please, ABAP expressions are not nearly as powerful as lambda, in fact I cannot even see a reference between both.. ABAP expressions are just simple expressions, getting better but not even close.

      it's a problem with semantics and it looks like you have had yours beaten out of your brain: "loop. loop. endloop. endloop." is a nested loop, it's quite clear and I hope you don't challenge that..

      now, if yet another keyword was added "group into a. loop a. endloop." my eyes and my brain would not see this as a nested loop.. not to say, I think it would also make it more readable.

      Cheers,

      D.

      Author's profile photo Former Member
      Former Member

      Hello Horst,

      Unfortunately i have to wait till January for our sandbox to get updated to the latest ABAP release.

      A few of questions -

      1. You have said that the first loop(on the internal table) will iterate over all the lines, do we have to worry about the performance?
      2. Does the GROUP BY behave similarly for different internal table types?
      3. So i understand <group> is like an temporary internal table. What happens to it after the LOOP iteration is over?

      BR,

      Suhas

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author
      1. All the lines you want to iterate. You can use WHERE conditions for all LOOPs and FORs involved.
      2. The implicit grouping loop is internally carried out like a normal LOOP AT itab for the different table types. There is no impact on calculating the group key but on the order how the groups are filled. The latter can be influencd by ASCENDING or DESCIENDING. These additions open up new capabilities for sorting internal tables, by the way.
      3. It is deleted and not accesible any more.
      Author's profile photo Former Member
      Former Member

      Hi Horst,

      ATC reports "Nested Loop" finding when i use a LOOP AT GROUP. For illustration i copied DEMO_LOOP_GROUP_BY_VALUES to Z-namespace and checked it with ATC.

      Got the following finding -

      /wp-content/uploads/2016/04/2016_04_19_131506_933214.jpg

      Although this is Prio 2 finding, but IMO ATC (or rather Code Inspector) must not be reporting this.

      Any ideas?

      BR,

      Suhas

      PS - I replaced LOOP AT GROUP with FOR...IN GROUP and ATC doesn't report it 😎

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

      Tell that to the Code Inspector ... 😛

      (Ignorare human est)

      Author's profile photo Former Member
      Former Member

      I thought SAP must have adjusted the CI checks so that LOOP AT GROUP isn't reported as nested loop. Anyway, it is not an ABAP language problem but an application problem 😉

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

      In fact, there are no CI warnings for that in my 7.40/7.50 systems. -> Those checks seem to be not too relevant.

      Author's profile photo Scott Lawton
      Scott Lawton

      Hi Suhas,

      I know your comment is quite old at this point, but we are just implementing ATC and are getting the same warning when using LOOP AT GROUP. Since Horst said he didn't get the error in his system, I suspect there must be a note out there somewhere that resolves the issue, but I haven't been able to find it yet. Did you ever have any luck?

      Thanks,

      Scott

      Author's profile photo Former Member
      Former Member

      Hi Horst,

      Really nice and interesting feature. Recently, i have to aggregate the data in an internal table and do sum on one column , find the max and min for another column, where i did this (group by )by myself. Now i can replace this method with this new construct. Nevertheless, will there be any function like to sum/average on those internal fields by applying after group by will come soon or is it too much to expect 🙂 ?

      Vijayan B

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

      will there be any function like to sum/average on those internal fields

      In the moment, I don't know anything about such plans.

      Author's profile photo Former Member
      Former Member

      Hi Mr. Keller,

      any news on this topic?

      Greetings

      Maurice Straube

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

      nope ...

      Workarounds

      Author's profile photo Pruthviraj Dayam
      Pruthviraj Dayam

      may be allowing arithmetic operations based on group would have made it more modular .. just an idea! 😎

      Nice article Horst.

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      Kopf Aua

      (but not your fault 😆 )

      Nice as always, thank you!

      Author's profile photo Former Member
      Former Member

      My thoughts exactly. 😎

      Started SAP / ABAP 6yrs ago coming from a Java background, and always complained about the verbose syntax (no functional expressions, intermediate DATA symbols for no apparent reasons, ...).

      Now Mr. Keller seems to be determined to turn this around and give JavaScript & Scala a run for their money. How about Closures and Promises next?! 😉

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

      Now Mr. Keller seems to be determined to turn this around

      Not Mr. Keller but Mr. Bohlmann, who presented his stuff at TechEd himself this year, honor to whom honor is due.

      I'm only writing the documentation ...

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

      Since there has been some confusion what kind of loops take place here, let me add a little example, where I implement the same grouping task without and with GROUP BY.

      REPORT ...

      CLASS demo DEFINITION.
        PUBLIC SECTION.
          CLASS-METHODS:
            class_constructor,
            main,
            meth1,
            meth2.
        PRIVATE SECTION.
          CLASS-DATA:
            numbers TYPE TABLE OF i WITH EMPTY KEY,
            out     TYPE REF TO if_demo_output,
            limit1  TYPE i VALUE 3,
            limit2  TYPE i VALUE 6.
      ENDCLASS.

      CLASS demo IMPLEMENTATION.
        METHOD class_constructor.
          numbers = VALUE #( FOR j = 1 UNTIL j > 10 ( j ) ).
        ENDMETHOD.
        METHOD main.
          out = cl_demo_output=>new( ).

          meth1( ).
          out->display( ).

          meth2( ).
          out->display( ).
        ENDMETHOD.

        METHOD meth1.
          TYPES:
            BEGIN OF helper_struc,
              key  TYPE string,
              refs TYPE TABLE OF REF TO i WITH EMPTY KEY,
            END OF helper_struc,
            helper_tab TYPE TABLE OF helper_struc WITH EMPTY KEY.

          DATA(helper_tab) = VALUE helper_tab(
           ( key = |below|   )
           ( key = |between| )
           ( key = |above|   ) ).

          "Grouping
          LOOP AT numbers REFERENCE INTO DATA(dref).

            IF dref->* < limit1.

              ASSIGN helper_tab[ key = |below| ]-refs TO FIELD-SYMBOL(<fs>).

              INSERT dref INTO TABLE <fs>.

            ELSEIF dref->* <= limit2.

              ASSIGN helper_tab[ key = |between| ]-refs TO <fs>.

              INSERT dref INTO TABLE <fs>.

            ELSE.

              ASSIGN helper_tab[ key = |above| ]-refs TO <fs>.

              INSERT dref INTO TABLE <fs>.

            ENDIF.

          ENDLOOP.

          "Group loop
          DATA members LIKE numbers.
          LOOP AT helper_tab INTO DATA(helper_struc).
            out->begin_section( helper_struc-key ).
            members = VALUE #( FOR m IN helper_struc-refs ( m->* ) ).
            out->write( members )->end_section( ).
          ENDLOOP.

        ENDMETHOD.

        METHOD meth2.
          DATA members LIKE numbers.
          LOOP AT numbers INTO DATA(number)
               GROUP BY COND string(
                 WHEN number <  limit1 THEN |below|
                 WHEN number <= limit2 THEN |between|
                 ELSE |above| )
               INTO DATA(group).
            out->begin_section( group ).
            members = VALUE #( FOR m IN GROUP group ( m ) ).
            out->write( members )->end_section( ).
          ENDLOOP.
        ENDMETHOD.
      ENDCLASS.

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

      The simple task is to group the numbers of an array type internal table into three groups.

      The first method meth1 works without GROUP BY. Instead the grouping is done explicitly in ABAP using a helper table helper_tab. After the grouping the lines of the nested table refs of the helper table point to the respective lines of the original table numbers for each group that is identified by the key column. After the first loop a second loop is carried out over the helper table and in each loop pass the nested tables refs are evaluated to access the group members.

      The second method meth2 works with GROUP BY. The result is exactly the same as for the first method. Comparing the methods, you can see that by GROUP BY the first loop in meth1, the grouping loop, is done implicitly and that in fact it is the second loop, the group loop, that you see here.

      As Kilian already stated above, we have three loops: the grouping loop, the group loop and the member loop (the latter, only if you want to). Using GROUP BY hides the grouping loop and the respective helper artifacts. Of course, you can always program it by yourself as shown for a simple example in meth1 but GROUP BY is more convenient and shorter as shown in meth2.

      Author's profile photo Oliver Jaegle
      Oliver Jaegle

      Dear Horst,

      I’m just becoming a huge fan of the group by, but honestly, the syntax is killing me. The things which are confusing me most (just posting some sample code in order to refer more easy to the components later-on):

      LOOP AT items INTO DATA(some_workarea)

            GROUP BY ( some_workarea-component = RHS )

              REFERENCE INTO DATA(group).

      "process groups

           

      The “outer” LOOP which “constructs” the groups refers to the items – which are actually not really looped at (but the distinct groups). Also, the many different workareas created/referred to were confusing me – particularly the one used in order to apply conditions for the grouping (in my sample some_workarea): It should not be used anywhere except in the brackets after the group by, since within the LOOP at groups, the group itself has a component for each criterion defining the group – am I right?

      If this – well – kind of confusing as the item of the group is actually not of relevance in the group-loop.

      If my understanding is correct, I wonder why the syntax does not read like this:

      GROUP items REFERENCE INTO DATA(group) BY (items-component = RHS ).

      "process groups

           

            LOOP AT MEMBERS OF GROUP group ASSIGNING FIELD-SYMBOL(<item>).

              "process the group members

            ENDLOOP.

      1. ENDGROUP.

      I’m sure you and the rocking colleagues have also thought of other syntaxes and “mine” is not too far-fetched, so I guess there must be a reason why you chose the existing notation.

      In any case, I don’t fully understand your comment that “this looks like nested loops, but it isn’t”. Also debugging it, it looks like a nested loop based on an intermediate table of groups (which is implicitly created). Is it actually comparable to the following old style AT:

      SORT items BY <group_criteria>.

      LOOP AT items INTO DATA(some_workarea).

            AT NEW <group criterion>.

                  CREATE DATA group TYPE HANDLE OF group_criteria.

                  MOVE-CORRESPONDING some_workarea TO group.

                  “Add the calculated values to group

            END AT.

            ASSIGN some_workarea TO <ls_item>.

      1. ENDLOOP.

      This would mean that the each member of items is actually accessed only once. What brings me to the question how the sorting, which would be necessary for the AT NEW, is performed - Particularly if a criterion is calculated in a method. What about secondary keys on the internal table which is grouped?

      If this was “compiled to AT”, is the part written after the LOOP AT members processed AT END OF?

      1. Looking forward to reading from you (though I fear I might not be able to fully understand it J ).

      Cheers,

      Oliver

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

      Hi Oliver,

      I guess there must be a reason why you chose the existing notation

      well, first it was simply for reuse reasons that the GROUP BY addition was added to the existing LOOP AT syntax instead of inventing a new keyword, because allmost all the features of LOOP AT (WHERE conditions etc.) are also needed for the grouping loop. Second, you cannot introduce new statements like GROUP in a downward compatible way, because there might be macros of that name.

      Of course GROUP BY is not merely an addition but defines a totally new variant of the existing LOOP AT statement. I had to learn all that in the hard way too. In the case of the FOR expression, the syntax FOR GROUPS ... OF goes more into the direction that you proposed.

      In any case, I don’t fully understand your comment that “this looks like nested loops, but it isn’t”.

      I said, "it isn't quiet that". All I wanted to point out is that the inner loop is not executed for each line of the internal table but for each group only. All in all, the GROUP BY stuff should be seen as something for your convenience that relieves you from doing it in ABAP yourself. There are different ways of achieving the same in ABAP. I have shown an example with IFs above, Of course, you mightt also use ATs, if the table is sorted appropriately.

      Regarding all the

      many different workareas created/referred to

      I (at least) tried to explain their meaning in the documentation, hopefully giving enough examples. Regarding the workarea/field-symbol/reference variable of the grouping loop, please be aware that it can be adressed in the loop if you use representative binding. Only if you work with group key binding (optionally defined by INTO GROUP), it  is initial within the loop.

      Best

      Horst

      Author's profile photo Edo von Glan
      Edo von Glan

      Sorry for the late entry into this discussion.

      Regarding the last issue in your answer, Horst:

      I think (I suppose similar to Oliver) that it would be great to make the workarea/field-symbol/reference variable of the grouping loop optional, because in >95% of cases you will not be using it outside of the GROUP BY-clause. (And it also makes the whole construct more difficult to understand for the novice, because it introduces an element, that is most likey not used.)

      Best regards, Edo

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

      Hi Edo,

      Sorry, but I don't get it.

      We cannot "make make the workarea/field-symbol/reference variable of the grouping loop optional", because you need it in the GROUP BY clause in order to define the group key. Syntactically you can define a group key without the result of the grouping loop but that's the 1% case (see an example). Therefore, TRANSPORTING NO FIELDS is not allowed. That's what I say in the documentation too: "The output behavior result for the table rows is still needed for the construction of the group key group_key ".

      Can you give a short example, how you would code a table dependend grouping without "workarea/field-symbol/reference variable of the grouping loop"?

      Horst

      Author's profile photo Edo von Glan
      Edo von Glan

      Hi Horst,

      thanks for asking again.

      I mean shortening the first LOOP statement in your original code like this:

      LOOP AT flights INTO DATA(flight)

        GROUP BY ( carrier = flight-carrid cityfr = flight-cityfrom )

         ASCENDING

         ASSIGNING FIELD-SYMBOL(<group>).

      I think this would be a good short form for a very common case. The case where

      • the result variable "flight" is not needed in the code after the first LOOP statement
      • and there is no necessity to map the field names (so "flight" can also be eliminated in the GROUP BY clause).

      Best regards, Edo

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

      Hi Edo,

      Now I see. As a short form it makes sense.

      Will forward the proposal to development.

      Cheers!

      Horst

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

      PS: If you like it short: FOR

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

      Hi Edo,

      "Development" does not like the idea of such a short form for several reasons.

      One important point is that in

      LOOP AT flights INTO DATA(flight)

        GROUP BY ( carrier = flight-carrid cityfr = flight-cityfrom )

      the RHS' of carrier = flight-carrid cityfr = flight-cityfrom are expression positions not just column names. Reducing it to mere column names would be another syntax variant, where you cannot write GROUP BY ( to_upper( carrid ) ) or something similar. Such a new syntax variant could confuse further. In the present state, you construct a group key with explicitly defined names (the LHS') and you can use these names inside the loop as the component names of <group>. 


      An already existiing simplification is the representative binding, where you leave away the INTO/ASSIGNING after GROUP BY. With that you reuse the INTO clause given before GROUP BY.



      With explicit INTO-clause for the group key:


      LOOP AT flights INTO DATA(flight1)

                GROUP BY ( carrier = flight1-carrid cityfr = flight1-cityfrom )

                         ASCENDING

                         INTO DATA(group).

             out->begin_section( |Group Key: { group-carrier }, { group-cityfr }| ).

             LOOP AT GROUP group ASSIGNING FIELD-SYMBOL(<flight1>).


      Reusing the INTO-clause:

      LOOP AT flights INTO DATA(flight2)

                GROUP BY ( carrier = flight2-carrid cityfr = flight2-cityfrom )

                         ASCENDING.

             out->begin_section( |Group Key: { flight2-carrid }, { flight2-cityfrom }| ).

             LOOP AT GROUP flight2 ASSIGNING FIELD-SYMBOL(<flight2>).

      Now you have only one INTO-clause!

      And last but not least: If you need only one column for the group key (seems to be a common use case), the syntax is close to that what you want:

      LOOP AT flights INTO DATA(flg) GROUP BY flg-carrid.

      Best

      Horst

      Author's profile photo Edo von Glan
      Edo von Glan

      ok. Thanks, and thanks for the other blog. Edo

      Author's profile photo Ralf Wenzel
      Ralf Wenzel

      I get really confused by collecting a bookmark list of some documentation parts, but I don't know how to get the address of a particular page , after right click I only get the information "JavaScript"..

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

      What kind of documentation do you mean? Regarding the ABAP Keyword Documentation, you can add ?file=name.htm to the URL to link to subnodes. Regarding SCN blogs I don´t have another recipe but searching for existing links and copy those. 

      Author's profile photo Ralf Wenzel
      Ralf Wenzel

      Yes, the keyword documentation. The question is: How to find the filename - in the browser's address field I can't find it....

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

      Mouse-over or right-click the node in the tree structure.

      Author's profile photo Ralf Wenzel
      Ralf Wenzel

      .....shows "JavaScript" 😉

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

      Only? I see more, e.g.

      javascript:call_link('abenabap_dictionary.htm')

      The name of the file is inside the brackets and can be placed behind ?file= in the URL..

      Author's profile photo Ralf Wenzel
      Ralf Wenzel

      I'll try it next week, I started my vacation one hour ago. 😉

      Author's profile photo Former Member
      Former Member

      Start with below link which has navigation tree on left pane:

      http://help.sap.com/abapdocu_740/en/index.htm

      Clicking any link opens content but does not change URL in address bar of browser.

      Using Firefox, I do right-click on right pane and This frame -> Show only this frame. Now I see the same content without the navigation tree.

      http://help.sap.com/abapdocu_740/en/ABENABAP.htm

      Clicking any link opens the content and gives you correct URL in address bar of browser.

      You can either do string substitution of javascript links or browse without navigation tree to see real URLs.

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

      I started my vacation 12 days ago ... 😆 .

      Author's profile photo Ralf Wenzel
      Ralf Wenzel

      This means, your current vacation is longer than all my vacations in 14 combined LOL Sry for being off topic

      Author's profile photo Kris Claes
      Kris Claes

      Horst,

      I'm trying this great feature in order to teach it to our developers (we are on SP9, so it should work).

      I copied you example rather literally, but I am still getting an error.

      Can you give me any idea what I do wrong?

      DATA lt_members TYPE STANDARD TABLE OF zat_my_table WITH EMPTY KEY.

         LOOP AT lt_table

           ASSIGNING FIELD-SYMBOL(<niv1f>)

           GROUP BY ( deel_3 = <niv1f>-deel_3

                      size = GROUP SIZE

                      index = GROUP INDEX )

                      ASCENDING

                      ASSIGNING FIELD-SYMBOL(<lr_niv1f>).

           lt_members = VALUE #( FOR m IN GROUP <lr_niv1f> ( m ) ).

         ENDLOOP.


      The error is "Type of "M" cannot be converted in "ZAT_MY_TABLE".

      How do I have to define this "m"?


      Thanks a lot for helping.

      Kris


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

      I don't see all of your declarations but it seems that lt_members is a table of a table. Why? Omit the TABLE OF in its declaration?

      Author's profile photo Kris Claes
      Kris Claes

      Thanks for the reply. I couldn't look at it earlier than now.

      I changed the declaration of IT_MEMBERS into:

        DATA lt_members LIKE lt_table. ... as in the exemple.

      And now it works fine. But I don't really see the difference between the two definitions.

      Thanks anyway.

      Kris

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

      But I don't really see the difference between the two definitions.

      Not?


      If you write TYPE TABLE OF table_type you get, a table of a table type, meaning a tabular line type. You must either write TABLE OF line_type or TYPE table_type or LIKE table_object. to get a table with the same line type as another table.


      Horst


      Author's profile photo Kris Claes
      Kris Claes

      That part I got.

      But the original definition was:

      DATA lt_members TYPE STANDARD TABLE OF zat_my_table WITH EMPTY KEY


      And "lt_table" in the LIKE has the same structure ...


      Maybe ABAP gets confused since "lt_table" is created dynamically in a SELECT-statement?


        SELECT uuid, getal_1, deel_2, deel_3

          FROM zat_my_table

          INTO TABLE @DATA(lt_table) .

      If I change the column-list into a '*', the original definition works also, as I notice now.

      Thanks for helping! 🙂

      Kris

      Author's profile photo Praveen Kumar Chitturi
      Praveen Kumar Chitturi

      Hi Horst,

      Thanks for your detailed information doc.

      I agree that with the GROUP BY , we can know the size of each group i.e. no.of records in each group. But is there any possibility to check the no. of different groups ? This is to identify unique group count.

       

      Thanks

      Praveen.

      Author's profile photo A. de Smidt
      A. de Smidt

      Dear Horst. we try to make the grouping multilevel but got stuck with below code.

      when we run this the  line

      ASSIGNING FIELD-SYMBOL(<level_2>).

      is blocking the assigning of level_1_details.

      when I remove the assigning field-symbol(<level_2>) everything works fine and .

      What do I miss in this new  concept of grouping that I can't level it up like this?

       

      LOOP AT lt_value_row INTO DATA(ls_level_1_header)
            GROUP BY ( level_1 = ls_level_1_header-level_1 )
              ASCENDING
              ASSIGNING FIELD-SYMBOL(<level_1>) .
        IF iv_show_level_1 = abap_true.
      * Get Level_2 and calculate the totals
          LOOP AT GROUP <level_1> ASSIGNING FIELD-SYMBOL(<level_1_details>)
             GROUP BY ( level_2 = <level_1_details>-level_2 )
               ASSIGNING FIELD-SYMBOL(<level_2>).
                if  <level_1_details> is ASSIGNED.
                      WRITE 'level1'.
                      WRITE <level_1_details>-level_1.
                      WRITE <level_1_details>-level_2.
                      WRITE <level_1_details>-level_3.
                      WRITE <level_1_details>-level_4.
                      WRITE <level_1_details>-value.
                      NEW-LINE.
                      total_level_1 = total_level_1 + <level_1_details>-value.
                      WRITE 'Totaal level 1:'.
                    WRITE total_level_1..
                    NEW-LINE. NEW-LINE.
                endif.
            IF iv_show_level_2 = abap_true.
      
              LOOP AT GROUP <level_2> INTO DATA(level_2_details)
                GROUP BY ( level_3 = level_2_details-level_3 )
                ASCENDING
                ASSIGNING FIELD-SYMBOL(<level_3>).
                    IF <level_2_details> IS ASSIGNED.
                      total_level_2 = total_level_2 + <level_2_details>-value.
                      WRITE 'level2'.
                      WRITE <level_2_details>-level_1.
                      WRITE <level_2_details>-level_2.
                      WRITE <level_2_details>-level_3.
                      WRITE <level_2_details>-level_4.
                      WRITE <level_2_details>-value.
                      NEW-LINE.
                    ENDIF.
      
                IF iv_show_level_3 = abap_true.
                  CLEAR total_level_3.
                  LOOP AT GROUP <level_3> ASSIGNING FIELD-SYMBOL(<level_3_details>)
                          GROUP BY ( level_4 = <level_3_details>-level_4 )
                          ASCENDING
                          ASSIGNING FIELD-SYMBOL(<level_4>).
                    IF <level_3_details> IS ASSIGNED.
                      total_level_3 = total_level_3 + <level_3_details>-value.
                      WRITE 'level3'.
                      WRITE <level_3_details>-level_1.
                      WRITE <level_3_details>-level_2.
                      WRITE <level_3_details>-level_3.
                      WRITE <level_3_details>-level_4.
                      WRITE <level_3_details>-value.
                      NEW-LINE.
                    ENDIF.
                    WRITE 'Totaal level 3:'.
                    WRITE total_level_3..
                    NEW-LINE. NEW-LINE.
      
                    IF iv_show_level_4 = abap_true .
                      CLEAR total_level_4.
                      LOOP AT GROUP <level_4> ASSIGNING FIELD-SYMBOL(<level_4_details>).
                        total_level_4 = total_level_4 + <level_4_details>-value.
                        WRITE 'niveau4'.
                        WRITE <level_4_details>-level_1.
                        WRITE <level_4_details>-level_2.
                        WRITE <level_4_details>-level_3.
                        WRITE <level_4_details>-level_4.
                        WRITE <level_4_details>-value.
      
                        NEW-LINE.
                      ENDLOOP.
                      WRITE 'Totaal level 4:'.
                      WRITE total_level_4..
                      NEW-LINE. NEW-LINE.
      
                    ENDIF.
                  ENDLOOP.
                ENDIF.
              ENDLOOP.
            ENDIF.
          ENDLOOP.
        ENDIF.
      ENDLOOP.

      Thanks Arthur

       

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

      Does the following help?

      https://blogs.sap.com/2016/06/23/group-by-for-internal-tables-step-by-stepc/

      Author's profile photo A. de Smidt
      A. de Smidt

      It also didn't help when I first investigated it.

      Problem with multilevel is that apparently you cant stack the grouping and access the data.

      Now problem solved to loop through the group 2 times 1 for adding levels and one to write data.

      If this logic of multilevel grouping can be solved more easily I would be glad to hear.

      below my test programm for the multilevel grouping and calculation

      thanks Arthur

      *&---------------------------------------------------------------------*
      *& Report ZLOOPGROUPBYTEST
      *&---------------------------------------------------------------------*
      *&
      *&---------------------------------------------------------------------*
      REPORT zloopgroupbytest.
      
      DATA:
        iv_show_lev_1	TYPE xfeld,
        iv_show_lev_2	TYPE xfeld,
        iv_show_lev_3	TYPE xfeld,
        iv_show_lev_4	TYPE xfeld.
      
      DATA: total_lev_1 TYPE wkgxxx.
      DATA: total_lev_2 TYPE wkgxxx.
      DATA: total_lev_3 TYPE wkgxxx.
      DATA: total_lev_4 TYPE wkgxxx.
      
      
      TYPES:
        BEGIN OF  lty_s_values_row,
          lev_1 TYPE text20,
          lev_2 TYPE text20,
          lev_3 TYPE text20,
          lev_4 TYPE text20,
          value TYPE wkgxxx,
        END OF lty_s_values_row .
      
      DATA: lt_value_row TYPE STANDARD TABLE OF lty_s_values_row.
      DATA: ls_value_row TYPE lty_s_values_row.
      
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2A'. ls_value_row-lev_3 = 'lev 3A'. ls_value_row-lev_4 = 'lev 4A'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2A'. ls_value_row-lev_3 = 'lev 3A'. ls_value_row-lev_4 = 'lev 4A'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2B'. ls_value_row-lev_3 = 'lev 3B'. ls_value_row-lev_4 = 'lev 4A'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2B'. ls_value_row-lev_3 = 'lev 3B'. ls_value_row-lev_4 = 'lev 4B'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2B'. ls_value_row-lev_3 = 'lev 3B'. ls_value_row-lev_4 = 'lev 4B'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2B'. ls_value_row-lev_3 = 'lev 3B'. ls_value_row-lev_4 = 'lev 4B'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2B'. ls_value_row-lev_3 = 'lev 3B'. ls_value_row-lev_4 = 'lev 4A'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2B'. ls_value_row-lev_3 = 'lev 3A'. ls_value_row-lev_4 = 'lev 4A'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2B'. ls_value_row-lev_3 = 'lev 3A'. ls_value_row-lev_4 = 'lev 4D'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2A'. ls_value_row-lev_3 = 'lev 3C'. ls_value_row-lev_4 = 'lev 4C'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2B'. ls_value_row-lev_3 = 'lev 3C'. ls_value_row-lev_4 = 'lev 4A'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2B'. ls_value_row-lev_3 = 'lev 3C'. ls_value_row-lev_4 = 'lev 4A'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2A'. ls_value_row-lev_3 = 'lev 3A'. ls_value_row-lev_4 = 'lev 4A'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      ls_value_row-lev_1 = 'lev 1'. ls_value_row-lev_2 = 'lev 2A'. ls_value_row-lev_3 = 'lev 3A'. ls_value_row-lev_4 = 'lev 4A'. ls_value_row-value = 10. APPEND ls_value_row TO lt_value_row.
      
      iv_show_lev_1 = abap_true.
      iv_show_lev_2 = abap_true.
      iv_show_lev_3 = abap_true.
      iv_show_lev_4 = abap_true.
      
      LOOP AT lt_value_row INTO DATA(ls_lev_1_header)
            GROUP BY ( lev_1 = ls_lev_1_header-lev_1 )
               ASSIGNING FIELD-SYMBOL(<lev_1>) .
      
        IF iv_show_lev_1 = abap_true.
      * Get lev_2 and calculate the totals
          LOOP AT GROUP <lev_1> ASSIGNING FIELD-SYMBOL(<lev_1_details>)
             GROUP BY ( lev_1 = <lev_1_details>-lev_1 lev_2 = <lev_1_details>-lev_2 )
               ASSIGNING FIELD-SYMBOL(<lev_2>).
      
            IF iv_show_lev_2 = abap_true.
      
              LOOP AT GROUP <lev_2> INTO DATA(lev_2_details)
                GROUP BY ( lev_1 = lev_2_details-lev_1 lev_2 = lev_2_details-lev_2 lev_3 = lev_2_details-lev_3 )
                ASSIGNING FIELD-SYMBOL(<lev_3>).
      
                IF iv_show_lev_3 = abap_true.
                  CLEAR total_lev_3.
                  LOOP AT GROUP <lev_3> ASSIGNING FIELD-SYMBOL(<lev_3_details>)
                          GROUP BY ( lev_1 = lev_2_details-lev_1 lev_2 = lev_2_details-lev_2 lev_3 = lev_2_details-lev_3 lev_4 = <lev_3_details>-lev_4 )
                          ASSIGNING FIELD-SYMBOL(<lev_4>).
      
                    NEW-LINE. NEW-LINE.
      
                    IF iv_show_lev_4 = abap_true .
                      CLEAR total_lev_4.
                      LOOP AT GROUP <lev_4> ASSIGNING FIELD-SYMBOL(<lev_4_details>).
                        total_lev_4 = total_lev_4 + <lev_4_details>-value.
                        WRITE AT 30 'lev-4'. WRITE AT 40 <lev_4_details>-lev_1. WRITE AT 50 <lev_4_details>-lev_2. WRITE AT 60 <lev_4_details>-lev_3. WRITE AT 70 <lev_4_details>-lev_4.
                      ENDLOOP.
      *                WRITE at 80 'Totaal lev 4:'.
                      WRITE AT 100 total_lev_4.
                      total_lev_4 = 0.
                      NEW-LINE.
                    ENDIF.
                  ENDLOOP. "level 3
      * loop second time through group for totalising details of group 3
                  LOOP AT GROUP <lev_3> INTO DATA(lev_3_det) .
                    total_lev_3 = total_lev_3 + lev_3_det-value.
                  ENDLOOP.
                  WRITE AT 15 'TOTAL level 3'. WRITE AT 40 lev_3_det-lev_1. WRITE AT 50 lev_3_det-lev_2. WRITE AT 60 lev_3_det-lev_3. WRITE AT 100 total_lev_3.
                  NEW-LINE. NEW-LINE.
                  total_lev_3 = 0.
      
                ENDIF.
              ENDLOOP.
      * loop second time through group for totalising details of group 2
              LOOP AT GROUP <lev_2> INTO DATA(lev_2_det).
                total_lev_2 = total_lev_2 + lev_2_det-value.
              ENDLOOP.
              WRITE AT 10 'TOTAL level 2'. WRITE AT 40 lev_2_det-lev_1. WRITE AT 50 lev_2_det-lev_2. WRITE AT 100 total_lev_2. NEW-LINE. NEW-LINE.
              total_lev_2 = 0.
            ENDIF.
          ENDLOOP.
          LOOP AT GROUP <lev_1> INTO DATA(lev_1_det).
            total_lev_1 = total_lev_1 + lev_1_det-value.
          ENDLOOP.
          WRITE AT 1 'TOTAL level 1'.  WRITE AT 20 lev_1_det-lev_1. WRITE AT 100 total_lev_1.  NEW-LINE.
          total_lev_1 = 0.
        ENDIF.
      ENDLOOP.

       

      Author's profile photo jaswanth pailla
      jaswanth pailla

      Hi Keller ,

      My question might not sound correct , but please answer me .

      From this blog i came to understand group by is introduced instead of At new and some other such features . Is it right ?

      Second thing I tried to implement parallel cursor using this group by like this

      Loop at lt_vbak into data(ls_vbak).

      LOOP AT lt_vbap INTO DATA(ls_vbap)
      GROUP BY ( vbeln = ls_vbak-vbeln )  - here i have used header vbeln
      ASCENDING
      ASSIGNING FIELD-SYMBOL(<group>).

      but it is not working correctly could you please say will it work

       

      Author's profile photo Junjie Pan
      Junjie Pan

      I tested and found that the traditional way "LOOP inside LOOP" has better performance than "LOOP ... GROUP BY".

      * 1) LOOP ... GROUP BY  
      LOOP AT lt_spfli INTO DATA(ls_spfli)
          GROUP BY ( carrier = ls_spfli-carrid city_from = ls_spfli-cityfrom )
          ASSIGNING FIELD-SYMBOL(<fs_group>).
      
          CLEAR: lt_member.
          LOOP AT GROUP <fs_group> ASSIGNING FIELD-SYMBOL(<fs_grp_spfli>).
            MOVE-CORRESPONDING <fs_grp_spfli> TO ls_member.
            APPEND ls_member TO lt_member.
          ENDLOOP.
        ENDLOOP.
      
      *===================================================================
      * 2) LOOP inside LOOP
        LOOP AT lt_spfli INTO ls_spfli.
          ls_group-carrid = ls_spfli-carrid.
          ls_group-cityfrom = ls_spfli-cityfrom.
          COLLECT ls_group INTO lt_group.
        ENDLOOP.
      
        LOOP AT lt_group INTO ls_group.
          CLEAR: lt_member.
          LOOP AT lt_spfli INTO ls_spfli WHERE carrid   = ls_group-carrid
                                           AND cityfrom = ls_group-cityfrom.
            MOVE-CORRESPONDING ls_spfli TO ls_member.
            APPEND ls_member TO lt_member.
          ENDLOOP.
        ENDLOOP.

       

      Below is the performance comparison:

      lt_spfli%20has%2028%20lines

      lt_spfli has 28 lines

       

      lt_spfli%20has%2014%2C680%2C064%20lines

      lt_spfli has 14,680,064 lines

       

      It seems this new feature doesn't have better performance.

      Author's profile photo Sandra Rossi
      Sandra Rossi

      Interesting and weird. I don't see a good reason why the performance in the kernel would be so slow (3x). Maybe there's something else. I'll test that. Thank you for the warning!

      Author's profile photo Sandra Rossi
      Sandra Rossi

      I can reproduce partially the additional cost on my SAP system (7.52 SP 0/ kernel 753 SP 300), but it's only 1.5x.

      On my system, the additional cost is due to the construction of the "internal internal table", because with the below code I reproduce the GROUP BY behavior with old ABAP, and the performance is almost identical, so we must keep that in mind when we work with high volume of data:

      CLASS test DEFINITION FOR TESTING DURATION SHORT RISK LEVEL HARMLESS.
        PRIVATE SECTION.
          METHODS group_by FOR TESTING.
          METHODS classic FOR TESTING.
          CLASS-METHODS class_setup.
          CLASS-DATA lt_spfli TYPE SORTED TABLE OF spfli WITH NON-UNIQUE KEY carrid cityfrom.
          TYPES: BEGIN OF ty_group,
                   carrid   TYPE spfli-carrid,
                   cityfrom TYPE spfli-cityfrom,
                   lines    TYPE STANDARD TABLE OF i WITH EMPTY KEY,
                 END OF ty_group,
                 ty_groups TYPE HASHED TABLE OF ty_group WITH UNIQUE KEY carrid cityfrom.
          DATA: ls_spfli  TYPE spfli,
                ls_member TYPE spfli,
                lt_member TYPE TABLE OF spfli,
                ls_group  TYPE ty_group,
                lt_group  TYPE ty_groups.
      ENDCLASS.
      
      CLASS test IMPLEMENTATION.
      
        METHOD class_setup.
          SELECT * FROM spfli INTO TABLE lt_spfli.
      
          WHILE lines( lt_spfli ) < 1000000.
            INSERT LINES OF lt_spfli INTO TABLE lt_spfli.
          ENDWHILE.
        ENDMETHOD.
      
        METHOD group_by.
      
          LOOP AT lt_spfli INTO DATA(ls_spfli)
              GROUP BY ( carrier = ls_spfli-carrid city_from = ls_spfli-cityfrom )
              ASSIGNING FIELD-SYMBOL(<fs_group>).
      
            CLEAR: lt_member.
            LOOP AT GROUP <fs_group> ASSIGNING FIELD-SYMBOL(<fs_grp_spfli>).
      **        MOVE-CORRESPONDING <fs_grp_spfli> TO ls_member.
      **        APPEND ls_member TO lt_member.
            ENDLOOP.
          ENDLOOP.
      
        ENDMETHOD.
      
        METHOD classic.
      
          LOOP AT lt_spfli INTO ls_spfli.
            DATA(tabix) = sy-tabix.
            ls_group-carrid = ls_spfli-carrid.
            ls_group-cityfrom = ls_spfli-cityfrom.
            READ TABLE lt_group
                  WITH TABLE KEY carrid = ls_group-carrid cityfrom = ls_group-cityfrom
                  ASSIGNING FIELD-SYMBOL(<ls_group>).
            IF sy-subrc <> 0.
              INSERT ls_group INTO TABLE lt_group ASSIGNING <ls_group>.
            ENDIF.
            APPEND tabix TO <ls_group>-lines.
      *      ls_group-carrid = ls_spfli-carrid.
      *      ls_group-cityfrom = ls_spfli-cityfrom.
      *      COLLECT ls_group INTO lt_group.
          ENDLOOP.
      
          LOOP AT lt_group INTO ls_group.
            CLEAR: lt_member.
            LOOP AT ls_group-lines ASSIGNING FIELD-SYMBOL(<line>).
            ENDLOOP.
      *      LOOP AT lt_spfli INTO ls_spfli WHERE carrid   = ls_group-carrid
      *                                       AND cityfrom = ls_group-cityfrom.
      **        MOVE-CORRESPONDING ls_spfli TO ls_member.
      **        APPEND ls_member TO lt_member.
      *      ENDLOOP.
          ENDLOOP.
        ENDMETHOD.
      ENDCLASS.

       

      Author's profile photo Junjie Pan
      Junjie Pan

      Yes, you can try to run multiple times.

      Sometimes 1.5x, however, sometimes 2x or even 3x.