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: 

Inner Joins with Group by in SELECT

Former Member
0 Kudos

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.

3 REPLIES 3

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

Former Member
0 Kudos

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