10-25-2005 4:34 PM
Hi Friends,
Is it possible to have an aggregate function while using Inner join and GROUP BY? Its not working for me.
Prompt replies would be rewarded.
Thanks in advance.
Regards
Tamilarasan.
Message was edited by: Tamilarasan Lakshmanan
10-25-2005 4:51 PM
I'm not sure what you mean by 'not working', but you can try something like:
* select data: parked documents in value type 54 (invoice) --------
select fmifiit~knbelnr bkpf~bstat
count( distinct fmifiit~fmbelnr )
into (wa_error-belnr, wa_error-bstat, wa_error-count)
from fmifiit inner join bkpf
on ( fmifiit~bukrs = bkpf~bukrs and
fmifiit~kngjahr = bkpf~gjahr and
fmifiit~knbelnr = bkpf~belnr )
where fmifiit~knbelnr in s_belnr
and fmifiit~bukrs = p_bukrs
and fmifiit~kngjahr = p_gjahr
and ( fmifiit~wrttp = '54' or
fmifiit~wrttp = '57' or
fmifiit~wrttp = '66' )
and bkpf~bukrs = p_bukrs
and bkpf~gjahr = p_gjahr
and bkpf~belnr in s_belnr
and bkpf~budat in s_budat
and ( bkpf~bstat = 'V' or bkpf~bstat = 'W' or bkpf~bstat = 'Z' )
group by fmifiit~knbelnr
bkpf~bstat
order by fmifiit~knbelnr.
Rob
10-25-2005 4:51 PM
I'm seeing the same. In my sample program it is aggregating on the first column, but not collecting the quantity in the second column.
report zrich_0002.
data: begin of itab occurs 0,
gltrp type afko-gltrp,
psmng type afpo-psmng,
end of itab.
select-options: s_gltrp for itab-gltrp.
select afko~gltrp sum( afpo~psmng )
into corresponding fields of table itab
from afko
inner join afpo
on afko~aufnr = afpo~aufnr
where afko~gltrp in s_gltrp
group by afko~gltrp.
check sy-subrc = 0.
Regards,
Rich Heilman
10-25-2005 4:51 PM
I'm not sure what you mean by 'not working', but you can try something like:
* select data: parked documents in value type 54 (invoice) --------
select fmifiit~knbelnr bkpf~bstat
count( distinct fmifiit~fmbelnr )
into (wa_error-belnr, wa_error-bstat, wa_error-count)
from fmifiit inner join bkpf
on ( fmifiit~bukrs = bkpf~bukrs and
fmifiit~kngjahr = bkpf~gjahr and
fmifiit~knbelnr = bkpf~belnr )
where fmifiit~knbelnr in s_belnr
and fmifiit~bukrs = p_bukrs
and fmifiit~kngjahr = p_gjahr
and ( fmifiit~wrttp = '54' or
fmifiit~wrttp = '57' or
fmifiit~wrttp = '66' )
and bkpf~bukrs = p_bukrs
and bkpf~gjahr = p_gjahr
and bkpf~belnr in s_belnr
and bkpf~budat in s_budat
and ( bkpf~bstat = 'V' or bkpf~bstat = 'W' or bkpf~bstat = 'Z' )
group by fmifiit~knbelnr
bkpf~bstat
order by fmifiit~knbelnr.
Rob
10-25-2005 4:57 PM
Ok, I got my sample working now. Check it out. I didn't like the "into corresponding fields"
report zrich_0002.
data: begin of itab occurs 0,
gltrp type afko-gltrp,
psmng type afpo-psmng,
end of itab.
select-options: s_gltrp for itab-gltrp.
select afko~gltrp sum( afpo~psmng )
into table itab
from afko
inner join afpo
on afko~aufnr = afpo~aufnr
where afko~gltrp in s_gltrp
group by afko~gltrp.
check sy-subrc = 0.
Regards,
Rich Heilman
10-25-2005 4:52 PM
Hi Tamilarasan,
Hope this helps.
http://help.sap.com/saphelp_47x200/helpdata/en/44/78baf6f7d2424fa78fd67860a18b26/frameset.htm
10-25-2005 6:22 PM