topic Summing several fields with SELECT SUM in Application Development and Automation Discussions

Summing several fields with SELECT SUM

Former Member — Thu, 17 Jul 2008 11:38:12 GMT

Hi abappers.

I'm going BONKERS on this silly problem which would take all of about 2 secs to do in something like MySQL.

The wretched examples with that silly airline application in the SAP help is worse than useless. In all my years of working with SAP I don't think I've EVER found a SINGLE instance of finding a solution to any prioblem I've ever had from the Airline application.

I've got a table which is sorted on Plant (WERKS) and Material (NATNR) and some quantity fields fror financial period.

There's sevral records per plant/material.

I want to sum up 2 quantity fields per plant / material.

This code gives me an error - comma without preceding colon (after SELECT ?).



data: lv_werks   type msku-werks,
      lv_matnr   type mara-matnr,
      lv_sum     type msku-kulab.


loop at t_horizontal into wa_ty_horizontal.
    select werks matnr sum( kulab,kuins )
    into  (lv_werks, lv_matnr, lv_sum )
      from msku
      where matnr eq wa_ty_horizontal-matnr
      and werks eq wa_ty_horizontal-werks
      group by werks matnr.
  endselect.
  * process total value  some other non relevant code here
endloop.

This works but only sums up ONE field -- I want the aggregate of BOTH fields however.



loop at t_horizontal into wa_ty_horizontal.
    select werks matnr sum( kulab  )
    into  (lv_werks, lv_matnr, lv_sum )
      from msku
      where matnr eq wa_ty_horizontal-matnr
      and werks eq wa_ty_horizontal-werks
      group by werks matnr.
  endselect.
  * process total value  some other non relevant code here
endloop.

the object is to avoid having to sum the two fields manually

Thanks

jimbo.

Re: Summing several fields with SELECT SUM

Former Member — Thu, 17 Jul 2008 11:45:28 GMT

Hai,

Dont get irritated dude.

data: lv_werks type msku-werks,

lv_matnr type mara-matnr,

lv_sum type msku-kulab.

data:begin of it_sum occurs 0 ,

lv_werks type msku-werks,

lv_matnr type mara-matnr,

lv_sum type msku-kulab,

end of it_sum.

loop at t_horizontal into wa_ty_horizontal.

select werks matnr kulab kuins

into it_sum

from msku

where matnr eq wa_ty_horizontal-matnr

and werks eq wa_ty_horizontal-werks

group by werks matnr.

collect it_sum .

endselect.

process total value some other non relevant code here

endloop.

Neeraj

Re: Summing several fields with SELECT SUM

former_member787646 — Thu, 17 Jul 2008 11:49:19 GMT

Hi,

Try this......

data: lv_werks type msku-werks,

lv_matnr type mara-matnr,

lv_sum_1 type msku-kulab,

lv_sum_2 type msku-kulab.

loop at t_horizontal into wa_ty_horizontal.

select werks matnr sum( kulab ) sum( kuins )

into (lv_werks, lv_matnr, lv_sum_1, lv_sum_2 )

from msku

where matnr eq wa_ty_horizontal-matnr

and werks eq wa_ty_horizontal-werks

group by werks matnr.

endselect.

lv_sum_1 = lv_sum_1 + lv_sum_2.

process total value some other non relevant code here

endloop.

Hope this would help you.

Murthy

Re: Summing several fields with SELECT SUM

ThomasZloch — Thu, 17 Jul 2008 11:50:34 GMT

Well, if you can't work it out, who could?

I can only think of

select werks matnr sum( kulab ) sum( kuins )
    into  (lv_werks, lv_matnr, lv_sum_kulab, lv_sum_kuins )

but you'd still have to add the two totals...

Thomas

Re: Summing several fields with SELECT SUM

Former Member — Thu, 17 Jul 2008 11:56:00 GMT

Hi there

I wanted to avoid the use of Collect -- the aggregate function SUM should do this with ONE READ per material plant combination. There are roughly 50 lines per materal plant combination.

For 50,000 materials this would amount to 2,500,000 reads PER PLANT instead of 50,000.

If you have a large number of plants the data base access time increases dramatically - this is why I specifically want to use the AGGREGATE function SUM

Thanks for the other replies -- it seems a shame that you still have to add the total together but at least it's better than the COLLECT solution someone else proposed.

Any self respecting database system should have a SUM multiple fields facility in it.

Cheers

Jimbo.