topic Re: how to use the SQL group by functionality on internal table. in Application Development and Automation Discussions

how to use the SQL group by functionality on internal table.

Former Member — Tue, 21 Jul 2009 11:56:01 GMT

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.

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

Re: how to use the SQL group by functionality on internal table.

Former Member — Tue, 21 Jul 2009 12:21:16 GMT

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.

.......

.......

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.

Re: how to use the SQL group by functionality on internal table.

Former Member — Wed, 22 Jul 2009 06:25:25 GMT

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.

Re: how to use the SQL group by functionality on internal table.

Former Member — Wed, 22 Jul 2009 22:30:19 GMT

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.

Re: how to use the SQL group by functionality on internal table.

Former Member — Thu, 23 Jul 2009 04:31:42 GMT

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.

Re: how to use the SQL group by functionality on internal table.

former_member206439 — Thu, 23 Jul 2009 04:48:40 GMT

By useing AT NEW and AT LAST events in a LOOP of internal table you get all group of values together

Re: how to use the SQL group by functionality on internal table.

Former Member — Thu, 23 Jul 2009 12:27:21 GMT

hi chathia,

can i get the max, min or average fuctionality by using the collect statement.

Regards

Nitin Varshney

Re: how to use the SQL group by functionality on internal table.

Former Member — Thu, 23 Jul 2009 12:32:47 GMT

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

Re: how to use the SQL group by functionality on internal table.

Former Member — Thu, 23 Jul 2009 12:33:41 GMT

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.

Re: how to use the SQL group by functionality on internal table.

Former Member — Thu, 23 Jul 2009 12:38:28 GMT

Can u please explain me.........

how i can get the min max or average on internal table using multiple column grouping.........

Regards

Nitin Varshney