topic Re: sum in select in Application Development and Automation Discussions

sum in select

Former Member — Fri, 25 Aug 2006 12:34:12 GMT

Hi all,

I have an internal table consisting of three fields. I want to populate the internal table using a select statment for where I sum all records returned by material. Here is my query without the select:

SELECT PRINS PRLAB PREIN

INTO TABLE GT_MSPR_FIELDS

FROM MSPR

FOR ALL ENTRIES IN GT_SUPPLY_DEMAND

WHERE MATNR EQ GT_SUPPLY_DEMAND-MATNR

AND WERKS EQ GT_SUPPLY_DEMAND-WERKS

AND PSPNR IN S_PSPNR.

I tried adding a SUM statment to each field but the select poops. Is there a way to do this?

regards,

Mat

Re: sum in select

Former Member — Fri, 25 Aug 2006 12:36:15 GMT

Hi, take a look :

The "collect" statement will do the sum.

data : GT_MSPR_FIELDS2 like GT_MSPR_FIELDS with header line.


loop at GT_MSPR_FIELDS2.
move-corresponding GT_MSPR_FIELDS to GT_MSPR_FIELDS2
collect GT_MSPR_FIELDS2.
endloop.

Best regards,

Erwan

Re: sum in select

Former Member — Fri, 25 Aug 2006 12:36:59 GMT

Hi,

Loop at the internal table.

use control break processing statments

write SUM

end the loop.

Regards,

Senthil

Re: sum in select

Former Member — Fri, 25 Aug 2006 12:39:05 GMT

In general you're better off letting the database do the summing since it is more efficient. Can you show us the SQL that gives you an error? Do you have a <b>GROUP BY</b> statement in the code?

Re: sum in select

Former Member — Fri, 25 Aug 2006 12:42:18 GMT

Hi Bjorn,

I have an itab that includes matnr prins prlab prein

SELECT MATNR sum(PRINS) sum(PRLAB) sum(PREIN)

INTO TABLE GT_MSPR_FIELDS

FROM MSPR

FOR ALL ENTRIES IN GT_SUPPLY_DEMAND

WHERE MATNR EQ GT_SUPPLY_DEMAND-MATNR

AND WERKS EQ GT_SUPPLY_DEMAND-WERKS

AND PSPNR IN S_PSPNR.

that is what I had in mind....thanks much.

Re: sum in select

Former Member — Fri, 25 Aug 2006 12:43:41 GMT

select matnr werks sum(prins) sum(prlab) sum(prein)

into table gt_mspr_fields

from mspr

for all entries in gt_supply_demand

where

MATNR EQ GT_SUPPLY_DEMAND-MATNR

AND WERKS EQ GT_SUPPLY_DEMAND-WERKS

AND PSPNR IN S_PSPNR

GROUP BY matnr werks.

Re: sum in select

Former Member — Fri, 25 Aug 2006 12:48:12 GMT

doesn't appear to like the for all entries in componenet of the select....

Re: sum in select

Former Member — Fri, 25 Aug 2006 12:56:21 GMT

In that case can you loop through the gt_supply internal table and use the particular select statement.

(Surely without for all entries clause)