Skip to Content
author's profile photo Former Member
Former Member

Inner Joins with Group by in SELECT

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 28, 2008 at 03: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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 28, 2008 at 03: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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 28, 2008 at 04: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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.