01-28-2008 3:11 AM
halo,
Is it possible to use GROUP BY addn. in select query which is using INNER JOINs?
i need to get sum of a currency field based on key and non-key fields.
code :
select bd~bukrs
bd~kunnr
bd~umskz
bd~zuonr
bd~gjahr
bd~belnr
bd~buzei
bd~bschl
bd~sgtxt
bd~prctr
kb~akont
bd~zterm
bk~xblnr
bk~hwaer
bd~shkzg
bd~mwskz
bd~dmbtr
bd~wrbtr
bk~waers into table gt_bxxx
from ( bsid as bd inner join
bkpf as bk
on bdbukrs = bkbukrs and
bdbelnr = bkbelnr and
bdgjahr = bkgjahr )
inner join knb1 as kb
on bdkunnr = kbkunnr and
bdbukrs = kbbukrs
where bd~bukrs in s_bukrs
and bd~gjahr in s_gjahr
and bd~kunnr in s_kunnr
and bd~umskz in s_umskz
and bd~belnr in s_belnr
and bd~prctr = space.
I need to sum up the DMBTR field based on BELNR(key field) and SHKZG(non-key field).Please suggest.
Thank you.
Regards,
Swaminathan.
01-28-2008 3:18 AM
Hi Swami nathan, u can use group by in select querry using inner joins.
You use GROUP BY when you want to make use of aggregate operations eq min( ), max( ), sum ( ) etc.
eg select a b sum( c )
from mytable
group by a b
Each field in the select statement either needs to be listed in the GROUP BY clause, or have an aggregate function applied to it.
kindly reward if found helpful.
cheers,
Hema.
01-28-2008 3:23 AM
Yes it is Possible , when you want to Group on a particular field in select Query itself.
REPORT demo_select_group_by.
DATA: carrid TYPE sflight-carrid,
minimum TYPE p DECIMALS 2,
maximum TYPE p DECIMALS 2.
SELECT carrid MIN( price ) MAX( price )
INTO (carrid, minimum, maximum)
FROM sflight
GROUP BY carrid.
WRITE: / carrid, minimum, maximum.
ENDSELECT.
Reward points if it is usefull ...
Girish
01-28-2008 4:01 AM
Hi,
Its possible, have a look at below Select...
SELECT mkal~mdv01
blpk~budat
blpk~werks
blpk~matnr
blpk~verid
blpk~pwerk
sum( blpk~menge )
blpk~meinh
INTO table l_t_BF
FROM blpk
inner join mkal on blpkmatnr = mkalmatnr and
blpkwerks = mkalwerks and
blpkverid = mkalverid
where mkal~werks = p_werks and
blpk~budat in s_date
group by
mkal~mdv01
blpk~budat
blpk~werks
blpk~matnr
blpk~verid
blpk~pwerk
blpk~meinh.
Hope it helps.
Praveen