topic Re: SUM in SELECT query in Application Development and Automation Discussions

SUM in SELECT query

Former Member — Fri, 02 May 2008 09:15:14 GMT

data : lv_zgdmeng TYPE i.

SELECT SUM( zgdmeng )

INTO lv_zgdmeng

FROM zgdpw

WHERE matnr EQ ls_prores-matnr

AND charg EQ lv_batch.

and in table zgdmeng is numc

i m getting an error saying sum can be used with numeric fields..

how to do this..

thanks and regards

Re: SUM in SELECT query

Former Member — Fri, 02 May 2008 09:19:53 GMT

hi Neha,

do this way...select entire data and loop through the data to calculate the sum of it ...


SELECT zgdmeng 
INTO table it_zgdpw
FROM zgdpw
WHERE matnr EQ ls_prores-matnr
AND charg EQ lv_batch.

loop at it_zgdpw.
  lv_zgdmeng = lv_zgdmeng + it_zgdpw-zgdmeng.
endloop.

Regards,

Santosh

Re: SUM in SELECT query

Former Member — Fri, 02 May 2008 09:20:19 GMT

1. select all the zgdmeng in a internal table.

SELECT zgdmeng

INTO table lt_zgdmeng

FROM zgdpw

WHERE matnr EQ ls_prores-matnr

AND charg EQ lv_batch.

2. The loop the table and add all the quantities.

Loop lt_zgdmeng to lw_zgdmeng.

tot_zgdmeng = tot_zgdmeng + lw_zgdmeng.

clear: lw_zgdmeng.

endloop.

Re: SUM in SELECT query

Former Member — Fri, 02 May 2008 09:27:52 GMT

i think SUM clause will work with I , F or P only not with NUMC ,so try it with the change .

reward if helpful.

Re: SUM in SELECT query

Former Member — Fri, 02 May 2008 09:30:49 GMT

Hi Neha,

just define lv_zgdmeng like ztablaename-zgdmeng

your problem will be resolved.

Gaurav

Re: SUM in SELECT query

Former Member — Fri, 02 May 2008 09:33:19 GMT

hi neha,

If you only want to use data for calculations, it is often more efficient to use the aggregate functions of the SELECT clause than to read the individual entries from the database and perform the calculations in the ABAP program.

Aggregate functions allow you to find out the number of values and find the sum, average, minimum, and maximum values. Following an aggregate expression, only its result is transferred from the database.

ex:-

DATA RESULT TYPE P DECIMALS 2.

SELECT <agg>( [DISTINCT] COL_2 )

INTO RESULT

FROM TEST.

WRITE RESULT.

The arithmetic operators AVG and SUM only work with numeric fields like f, i ,p not other than this .

*The spaces between the parentheses and the arguments of the aggregate expressions must not be left out.

thanks regard ,

sandeep patel

award point if it is helpful.