sum function in inner join

[Former Member]

Dear Gurus

I am using the following select statement with inner join. I have used sum function for quantity field but i couldn find where the data is going as in body part quantity is coming individually and header line is empty.

the code is given below.

select a_{knumv b}posnr sum( b~fkimg ) as fkimg

from vbrk as a inner join vbrp as b

on ( a_{vbeln = b}vbeln )

into corresponding fields of table itab1

where

b~erdat in fkdat

and posnr > 0

and fkimg > 0

group by a_{knumv b}posnr.

With regards

PARDEEP SHARMA

[Former Member]

Hi,

As you are doing the inner join it is not recomonded to use sum function due to performance issue.So fetch all the values in the internal table them collect internal table and you will gat all the consolidated values compairing all non numeric value. Else you can loop at the internal table and add the Invoiced Quantity into a local variable say l_ fking.

l_ fking = l_ fking + in_tab-fking.

finally after loop you will get the total Invoiced Quantity.

Regards,

[Former Member]

thanks for your reply but i dont want to use loop statement.

I want to get the dataas well as sum in a single select command.

but the question is where the value of sum( b~fkimg ) is going?

Is it possible to use aggregate function using inner join and for all entries??

with regards

pardeep sharma

Edited by: Pardeep Sharma on Feb 4, 2009 2:54 PM

[Former Member]

Hi Pradeep,

Your select querry just populates your internal table. To get the data row by row into you rwork area either use a loop on your internal table or use READ statement.

[Former Member]

hi,

u can also select the data first and then use AT new or at AT END OF field_name.

SUM.

.......

ENDAT.

http://www.sap-img.com/abap/inner-joins.htm

[former_member222860]

Hi Pradeep,

I didn't understand your question clearly,

Here i'm all the values of itab including fkimg

data: begin of itab1 occurs 0,
        knumv like vbrk-knumv,
        posnr like vbrp-posnr,
        fkimg like vbrp-fkimg,
      end of itab1.

data: itab_wa like itab1.

select a~knumv b~posnr sum( b~fkimg ) as fkimg
from vbrk as a inner join vbrp as b
on ( a~vbeln = b~vbeln )
into corresponding fields of table itab1
where
*b~erdat in fkdat
posnr > 0
and fkimg > 0
group by a~knumv b~posnr.


loop at itab1 into itab_wa.
  write:/ itab_wa-knumv, itab_wa-posnr, itab_wa-fkimg.
endloop.

Pl. clarify

thanks\

Mahesh

[Former Member]

thanks for your reply Reddy.

My question is i want to do the calculations using the total quantity only.

So I am trying to get the total quantity with one select. But from where the total quantity to be taken after the select statement.

I dont want to use loop.............endloop statements!!!!!!!!

With regards

Pardeep sharma

[former_member222860]

Hi Pradeep,

If i get u correct, ur exepecting SUM of all the records of the column like this:

data: val type i.
select SUM( fkimg ) into val from vbrp.
write:/ val.

I feel that is not possible with joins.

thanks\

Mahesh

[christine_evans]

>

> Hi Pradeep,
> 
> If i get u correct, ur exepecting SUM of all the records of the column like this:
> 
> 
data: val type i.
> select SUM( fkimg ) into val from vbrp.
> write:/ val.
> 
> I feel that is not possible with joins.
> 
> thanks\
> Mahesh

It is perfectly possible to do a SUM with a JOIN. In response to an earlier question, I don't think that using aggregate functions with FOR ALL ENTRIES is allowed.

[Former Member]

hi,

yes it is possible....see this for help ....

http://www.webdeveloper.com/forum/showthread.php?t=176144

thanks

[faisalatsap]

Hi,

Please Have a Look at the following Thread Please Have a look at my Reply

[Inner Join VBRK VBRP SUM FKIMG|;

Kind Regards,

Faisal

Edited by: Faisal Altaf on Feb 4, 2009 2:52 PM

[Former Member]

thanks Faisal

I did the same but from where to sum of fkimg after the select statement.

please help me after select statement.

With regards

Pardeep Sharma

[faisalatsap]

>

> thanks Faisal
> I did the same but from where to sum of fkimg after the select statement.
> please help me after select statement.
> 
> With regards
> Pardeep Sharma

Test the following Code it is already giving you the SUM of "FKIMG" Group By "KNUMV and POSNR".

TABLES: vbrk, vbrp.

SELECT-OPTIONS: fkdat for vbrp-erdat.

TYPES: BEGIN OF t_vbrk_vbrp,
  knumv LIKE vbrk-knumv,
  posnr LIKE vbrp-posnr,
  fkimg LIKE vbrp-fkimg,
END OF t_vbrk_vbrp.

DATA: it_vbrk_vbrp TYPE STANDARD TABLE OF t_vbrk_vbrp WITH HEADER LINE.

SELECT vbrk~knumv vbrp~posnr SUM( vbrp~fkimg ) AS fkimg
  INTO CORRESPONDING FIELDS OF TABLE it_vbrk_vbrp
  FROM vbrk INNER JOIN vbrp ON ( vbrk~vbeln = vbrp~vbeln )
  WHERE vbrp~erdat IN fkdat
    AND posnr > 0
    AND fkimg > 0
GROUP BY vbrk~knumv vbrp~posnr.

What you mean by After Select what you want to do after Select Please reply,

Kind Regards,

Faisal

[Former Member]

Hi Pradeep

I think ,its better to fetch the whole data into your internal table and do the SUM.

If you need the sum of individual key fields you can go for COLLECT ,which adds all the

numeric field.If you want the total of a particular field ,you can use SUM in the control

break statement AT LAST .

AT LAST.

SUM.

ENDAT.

I think this will be more usefull for your situation.

Regards

Menon

[christine_evans]

>

> Dear Gurus
> I am using the following select statement with inner join. I have used sum function for quantity field but i couldn find where the data is going as in body part quantity is coming individually and header line is empty.
> the code is given below.
> 
>   select aknumv bposnr sum( b~fkimg ) as fkimg
>   from vbrk as a inner join vbrp as b
>   on ( avbeln = bvbeln )
>   into corresponding fields of table itab1
>   where
>                b~erdat in fkdat
>              and   posnr > 0
>              and   fkimg > 0
>              group by aknumv bposnr.
> 
> With regards
> PARDEEP SHARMA

If you don't use INTO CORRESPONDING FIELDS and just give your receiving itab fields the same order as your select string and discard the as FKIMG I think it should work.

[Former Member]

Hi,

Check this Select query,

Eg.

select sum( menge ) as menge sum( effwr ) as effwr

from ekko as k inner join

ekpo as p on k_{ebeln = p}ebeln

into corresponding fields of itab

where bednr = itemp-bednr and p~loekz <> 'L'

and k_{ebeln <> itemp-ebeln and k}bstyp = 'F'

and k~bsart in bsart

and p~aedat between first_day and last_day.

Make ur select query like this, U'll get the data in header line.

[Former Member]

Hi Pradeep,

Take The hole data to an internal Table and then do the sum.

So if u want to sum the perticular Filed then use the control break statments

for this as ATLAST.

SUM

ENDAT.

thanks

N.Neelima.

[Former Member]

Hi,

try the like the given code below

types: begin of t_vbrk_vbrp,
  kunrg like vbrk-kunrg,
  sortl like kna1-sortl,
  regio like vbrk-regio,
  mvgr1 like vbrp-mvgr1,
  matkl like vbrp-matkl,
  mvgr5 like vbrp-mvgr5,
  vrkme like vbrp-vrkme,
  fkimg like vbrp-fkimg,
end of t_vbrk_vbrp.
 
data: it_vbrk_vbrp type standard table of t_vbrk_vbrp with header line,
      wa_it_vbrk_vbrp type t_vbrk_vbrp.
 
select vbrk~kunrg vbrk~regio vbrp~mvgr1 vbrp~matkl vbrp~mvgr5 vbrp~vrkme sum( vbrp~fkimg ) as fkimg "vbrp~lgort
      into corresponding fields of table it_vbrk_vbrp
      from vbrp
      inner join vbrk on ( vbrp~vbeln = vbrk~vbeln and vbrk~fksto = '' )
      where vbrk~fkart = 'ZCLA'
        and vbrk~regio = 'RM3'
        and vbrk~fkdat = '20091231'
        and vbrp~matkl = '001'
        and vbrp~pstyv in ('ZFUL','TAN')
        and vbrp~mvgr1 = 'P01'
        and vbrp~mvgr5 in ('B09','B10','B11','B12')
      group by vbrk~kunrg vbrk~regio vbrp~mvgr1 vbrp~matkl vbrp~mvgr5 vbrp~vrkme .

Regards

Ritesh J

[Former Member]

thanks all of you.....

points to all..............

i got the clue n i m closing this thread......

With regards

pardeep sharma