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

sum in select

Former Member — Wed, 06 Sep 2006 08:08:31 GMT

i have this select

SELECT mard_{einme mard}insme mard_{labst mard}matnr mard~retme

mard_{speme mard}umlme mard_{werks mara}matnr mara~meins

makt_{maktx makt}matnr marc_{eisbe marc}matnr mard~lgort

marc~prctr

INTO (mard-einme , mard-insme , mard-labst , mard-matnr , mard-retme

, mard-speme , mard-umlme , mard-werks , mara-matnr , mara-meins

, makt-maktx , makt-matnr , marc-eisbe , marc-matnr , mard-lgort

,marc-prctr)

FROM ( mard

INNER JOIN mara

ON mara_{matnr = mard}matnr

INNER JOIN makt

ON makt_{matnr = mara}matnr

INNER JOIN marc

ON marc_{matnr = makt}matnr )

WHERE mard~matnr IN matnr

and i want to have column that have the sum of 5 fields

i dont care if i gat the value for all the column per matnr

Re: sum in select

Former Member — Wed, 06 Sep 2006 08:12:28 GMT

Hi,

It is preferred to take the data of the join into an internal table, loop on this table and then summate the fields based on your criteria.

Regards,

Raj

Re: sum in select

Former Member — Wed, 06 Sep 2006 08:12:50 GMT

can i make

select sum(insme,apeme,.....)

from tables.

Re: sum in select

gopi_narendra — Wed, 06 Sep 2006 08:13:15 GMT

take them into a internal table and sum up using collect statement

Message was edited by: Gopi Narendra

Re: sum in select

Former Member — Wed, 06 Sep 2006 08:14:30 GMT

Heres an example of using SUM.

Regards,

Raj

Example

Output the number of passengers, the total weight and the average weight of luggage for all Lufthansa flights on 28.02.1995:

TABLES SBOOK.

DATA: COUNT TYPE I, SUM TYPE P DECIMALS 2, AVG TYPE F.

DATA: CONNID LIKE SBOOK-CONNID.

SELECT CONNID COUNT( * ) SUM( LUGGWEIGHT ) AVG( LUGGWEIGHT )

INTO (CONNID, COUNT, SUM, AVG)

FROM SBOOK

WHERE

CARRID = 'LH' AND

FLDATE = '19950228'

GROUP BY CONNID.

WRITE: / CONNID, COUNT, SUM, AVG.

ENDSELECT.

Re: sum in select

Former Member — Wed, 06 Sep 2006 08:18:01 GMT

Hi,

It would be better if you loop at it and then sum it.

Eg.

ITAB is of following structure

MATNR

QTY1

QTy2

QTY3

SUM -- The new field = qty1 + qty2 + qty3

Loop at itab into watab.

sum = watab-qty1 + watab-qty2 + watab-qty3.

modify itab from watab.

endloop.

Best regards,

Prashant

Re: sum in select

Former Member — Wed, 06 Sep 2006 08:20:04 GMT

yes but i talk about 5 fields together

the problem is that the report is dynamic so it difficult for me

Re: sum in select

Former Member — Wed, 06 Sep 2006 08:21:35 GMT

the data is

matnr speme insme ....

12345 10 10

12345 2 3

i want

sum of all of this in one line

Re: sum in select

Former Member — Wed, 06 Sep 2006 08:22:33 GMT

SELECT mard_{matnr sum(mard}labst) into (lmatnr, llabst)

from mard inner join mara on mard_{matnr = mara}matnr

where mard~matnr in s_matnr

group by mard~matnr.

Follow the above example to do the needful. If u need 5 fields together you can only do that after the select statement.

ltot = llabst + lx1 + lx2....

Message was edited by: Anurag Bankley

Re: sum in select

Former Member — Wed, 06 Sep 2006 08:25:59 GMT

Hi,

Do one thing.. in your select, add SUM( field ) for all those fields which you want to summate... remember to have the group by clause..

[ I guess you will be getting multiple records, you need to use SELECT..ENDSELECT OR Internal table...]

Regards,

Raj