07-21-2009 12:56 PM
hi..
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.
like
in SQL we use
select werks matnr maktx extwg lgort umren meins sum(menge) sum(prd_wat)
from itab
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.....
Regards
Nitin Varshney
07-21-2009 1:21 PM
Hello,
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.
ENDLOOP.
For more information youn can refer to following link:
http://help.sap.com/saphelp_nw04/helpdata/EN/fc/eb381a358411d1829f0000e829fbfe/content.htm
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.
Thanks,
Augustin.
07-22-2009 7:25 AM
You would use the following code replace SQL group functionality:
LOOP AT T INTO WA_T.
AT FIRST.
SUM.
...
ENDAT.
...
AT END OF fielda.
SUM.
...
ENDAT.
ENDLOOP.
About more information please refer to abap online help for ( AT - Control breaks with internal tables )
Note that:
you should sort the internal table by the group field key when using AT control statement within loop ...endloop.
07-22-2009 11:30 PM
Hello Nitin,
It is better to select all data into internal table and do the processing to get the sum or other calculation. This is better that using SELECT (MAX) etc.
The reason is, you are performing operation at abap level, not at database level.
Thanks,
Rupali.
07-23-2009 5:31 AM
Hi Nitin,
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.
Eg.
SELECT werks matnr maktx extwg lgort umren meins menge prd_wat
from DATABASE TABLE
INTO TABLE itab1.
Important
SORT itab1 BY werks matnr maktx extwg lgort umren meins.
loop itab1 into wa1.
collect wa1 into itab2.
endloop.
CLEAR itab1[].
Regards,
Chathia.
07-23-2009 1:27 PM
hi chathia,
can i get the max, min or average fuctionality by using the collect statement.
Regards
Nitin Varshney
07-23-2009 1:33 PM
Hi Nitin,
The COLLECT statement is used only for SUM operation. You will not be able to do MAX, MIN etc using this statement.
Regards,
Chathia.
07-23-2009 1:38 PM
Can u please explain me.........
how i can get the min max or average on internal table using multiple column grouping.........
Regards
Nitin Varshney
07-23-2009 5:48 AM
Hi
By useing AT NEW and AT LAST events in a LOOP of internal table you get all group of values together
07-23-2009 1:32 PM
hi naresh,
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.
pls clear.......
Regards
Nitin Varshney