Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Summing several fields with SELECT SUM

Former Member
0 Kudos

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.

1 ACCEPTED SOLUTION

ThomasZloch
Active Contributor
0 Kudos

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

4 REPLIES 4

Former Member
0 Kudos

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

0 Kudos

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.

former_member787646
Contributor
0 Kudos

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

ThomasZloch
Active Contributor
0 Kudos

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