can any body explain me to how to use the SQL group by functionality on internal table.
i want to use the aggregate function on internal table using the different columns of the table.
in SQL we use
select werks matnr maktx extwg lgort umren meins sum(menge) sum(prd_wat)
group by werks matnr maktx extwg lgort umren meins
how this functionality will work on internal table ?
similarly if i want to use the max, min or average of internal table column.
i just give you the sql statement above.
also just give me the some different ways to do this work.....
Group by functionality is only avaliable for ABAP SQL's and not for internal table but you can use AT..ENDAT statement in
LOOP AT ITAB...ENDLOOP to simulate the group by functionality.
Following are the various options available:
LOOP AT <itab>.
AT FIRST. ... ENDAT.
AT NEW <f1>. ...... ENDAT.
AT NEW <f2 >. ...... ENDAT.
<single line processing>
AT END OF <f2>. ... ENDAT.
AT END OF <f1>. ... ENDAT.
AT LAST. .... ENDAT.
For more information youn can refer to following link:
Also, for other aggregate function like min, max, sum you need to do processing using LOOP AT <ITAB>...ENDLOOP
No standard commands are avaliable in SAP ABAP for above functions.
You would use the following code replace SQL group functionality:
LOOP AT T INTO WA_T.
AT END OF fielda.
About more information please refer to abap online help for ( AT - Control breaks with internal tables )
you should sort the internal table by the group field key when using AT control statement within loop ...endloop.
You can also use COLLECT statement into another internal table for aggregating the data. This will aggregate all the Key Figures (Amount, Quantity etc numeric fields). But this might be slower in some cases, hence it is mandatory to SORT the Source internal table before using the COLLECT statement.
SELECT werks matnr maktx extwg lgort umren meins menge prd_wat
from DATABASE TABLE
INTO TABLE itab1.
SORT itab1 BY werks matnr maktx extwg lgort umren meins.
loop itab1 into wa1.
collect wa1 into itab2.
can u please explain how at new & at last work.
actually i have done it, but it is not clear.
how i can get sum or min max or average by grouping of different columns( more than 4 or 5 column in grouping)
means i want grouping the data on the basis of 4-5 columns.
like just i have told in my sql statement in last conversation.