topic Re: Aggregate Functions in SELECT in Application Development and Automation Discussions

Aggregate Functions in SELECT

Former Member — Tue, 25 Oct 2005 15:34:36 GMT

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

Re: Aggregate Functions in SELECT

RichHeilman — Tue, 25 Oct 2005 15:51:11 GMT

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

Re: Aggregate Functions in SELECT

Former Member — Tue, 25 Oct 2005 15:51:45 GMT

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

Re: Aggregate Functions in SELECT

Former Member — Tue, 25 Oct 2005 15:52:51 GMT

Hi Tamilarasan,

Hope this helps.

http://help.sap.com/saphelp_47x200/helpdata/en/44/78baf6f7d2424fa78fd67860a18b26/frameset.htm

Re: Aggregate Functions in SELECT

RichHeilman — Tue, 25 Oct 2005 15:57:16 GMT

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

Re: Aggregate Functions in SELECT

RichHeilman — Tue, 25 Oct 2005 17:22:56 GMT

Please make sure to award points for any helpful answers that might have helped you. If your problem is solved, please mark this post as solved. Thanks.

Regards,

Rich Heilman