sum in select

[Former Member]

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

<b>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</b>

[Former Member]

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

[Former Member]

can i make

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

from tables.

[gopi_narendra]

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

Message was edited by: Gopi Narendra

[Former Member]

Hi

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.

[Former Member]

yes but i talk about 5 fields together

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

[Former Member]

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

[Former Member]

the data is

matnr speme insme ....

12345 10 10

12345 2 3

i want

sum of all of this in one line

[Former Member]

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

Message was edited by: Anurag Bankley

[Former Member]

Hi,

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

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

Regards,

Raj