topic Re: select sum help in Application Development and Automation Discussions

select sum help

Former Member — Thu, 09 Aug 2007 13:42:05 GMT

hallow

i wont to do select sum statment and i dont now how to do it right

i wont to sum field kwert to on field l_kwert how i do that.

i dont now the syntax

thankes

i try

select sum (kwert)

from konv

into l_kwert

where knumv = l_knumv

and kschl = 'ZMTG'.

Re: select sum help

Former Member — Thu, 09 Aug 2007 13:44:51 GMT

Hi,

Here is the Example

SELECT SUM( cant )
FROM zcmtt_envase
INTO v_cant
WHERE docsap = p_tipo
AND ref = p_ti_zcmtt_envase-ref
AND matnr = ti_sumlips-matnr.

Regards

Sudheer

Re: select sum help

Former Member — Thu, 09 Aug 2007 13:54:04 GMT

hallow

itry like your sample but i have erorr

aggregate function and distinct are not supported in field lists for pooled and cluster table

what is wrong?

Re: select sum help

Former Member — Thu, 09 Aug 2007 13:54:49 GMT

Hi,

I just see one problem with your syntax, the SPACES :

SELECT SUM( kwert ) FROM...

You don't have a space after SUM, but two around kwert.

Regards,

Mathieu

Re: select sum help

former_member194669 — Thu, 09 Aug 2007 13:57:51 GMT

Hi,

As KONV is a cluster table you need to do like this way


select kwert from konv
   into table i_kwert 
   where knumv = l_knumv
   and kschl = 'ZMTG'. 
loop at i_kwert.
  v_kwert = v_kwert + i_kwert.
endloop.

aRs

Re: select sum help

Former Member — Thu, 09 Aug 2007 14:02:46 GMT

thankes

how i declare i_kwert as table

and v_kwert

Regards

Re: select sum help

AlexGiguere — Thu, 09 Aug 2007 14:08:20 GMT

Hi Shnya,

sum is an OPEN SQL aggregated function, you have to use the addition group by in youre select statment

select sum (kwert) from konv into l_kwert group by kwert

where knumv = l_knumv

and kschl = 'ZMTG'.

sometimes performances are not very good with aggregated functions,

another alternative, is to select all the master data you want, then summarize in a loop endloop with the sum statment.

loop.

at end of key fields

sum. append result to internal table

endloop.

hope it will helps,

thanks!

Re: select sum help

AlexGiguere — Thu, 09 Aug 2007 14:34:55 GMT

Hi, use group by in your select or do a loop endloop with at end of key field sum!

Alex

Re: select sum help

Former Member — Fri, 10 Aug 2007 10:53:26 GMT

hi,

remove the blank space between sum (kwert).

other wise use internal table ..

in internal table use

loop at itab.

at end of kwert.

sum.

endloop.

Message was edited by:

alka sachin

Re: select sum help

Former Member — Fri, 10 Aug 2007 10:55:39 GMT

hi,

remove the blank space between sum (kwert).

other wise use internal table ..

in internal table use

loop at itab.

at end of kwert.

sum.

endloop.

Re: select sum help

Former Member — Fri, 10 Aug 2007 11:03:41 GMT

as KONV is a cluster table, see

aggregate function and distinct are not supported in field lists for pooled and cluster table

you must use:

select kwert from konv

into table i_kwert

where knumv = l_knumv

and kschl = 'ZMTG'.

loop at i_kwert.

v_kwert = v_kwert + i_kwert.

endloop.

You should also use such a coding, in the case of buffered tables!

If you do not know hwo to define internal tables, then I would highly

recommend you to call the online help on internal tables.

Siegfried

Re: select sum help

varma_narayana — Fri, 10 Aug 2007 11:23:17 GMT

Hi...

This is the code for u.

Data : i_kwert type Table of konv with header line.

data : v_kwert type konv-kwert.

select kwert from konv

into table i_kwert

where knumv = l_knumv

and kschl = 'ZMTG'.

loop at i_kwert.

v_kwert = v_kwert + i_kwert.

endloop.

<b>Reward if Helpful.</b>