topic Re: Sum in Selection in Application Development and Automation Discussions

Sum in Selection

Former Member — Wed, 19 Dec 2007 09:30:03 GMT

Hi,

i want to sum the betrh-field and group teh rest.

but i doesn't work and i don't know why.

here's my select

SELECT

dimaiobpar~zgst AS gs

dimaiobpar~zstatus AS status

dfkkop~bukrs AS bukr

SUM( DISTINCT dfkkop~betrh ) AS betrh

dfkkop~gpart AS gpart

dfkkop~vtref AS vtref

dimabroker~brostmtyp AS mtyp

adrc~name1 AS n1

adrc~name2 AS n2

adrc~name3 AS n3

dimaiobpar~zabrtyp AS abrtyp

dimaiobpar~zvat AS vertart

dimaiobpar~azawe_x AS zrtyp

dfkkop~opbel AS opbel

t001~butxt AS ges

INTO CORRESPONDING FIELDS OF itab

FROM ( dfkkop

INNER JOIN dimaiobpar ON

dimaiobpar_{partner = dfkkop}gpart AND

dimaiobpar_{insobject = dfkkop}vtref

INNER JOIN dimabroker ON

dimabroker_{partner = dfkkop}gpart AND

dimabroker_{insobject = dfkkop}vtref

INNER JOIN but020 ON

but020_{partner = dfkkop}gpart

INNER JOIN adrc ON

adrc_{addrnumber = but020}addrnumber

INNER JOIN t001 ON

t001_{bukrs = dfkkop}bukrs )

WHERE

dimaiobpar~zstatus NE '1' AND

dfkkop~betrh NE '0' AND

dfkkop~gpart IN sel002 AND

dfkkop~vtref IN sel003 AND

but020~xdfadr = 'X'

GROUP BY zgst zstatus dfkkop~bukrs gpart vtref brostmtyp name1 name2

name3 zabrtyp zvat azawe_x opbel t001~butxt.

APPEND itab.

ENDSELECT.

regards,

tobias

Re: Sum in Selection

kesavadas_thekkillath — Wed, 19 Dec 2007 09:44:17 GMT

SELECT dimaiobpar~zgst AS gs

dimaiobpar~zstatus AS status

dfkkop~bukrs AS bukr

SUM( DISTINCT dfkkop~betrh ) AS betrh

dfkkop~gpart AS gpart

dfkkop~vtref AS vtref

dimabroker~brostmtyp AS mtyp

adrc~name1 AS n1

adrc~name2 AS n2

adrc~name3 AS n3

dimaiobpar~zabrtyp AS abrtyp

dimaiobpar~zvat AS vertart

dimaiobpar~azawe_x AS zrtyp

dfkkop~opbel AS opbel

t001~butxt AS ges

APPENDING CORRESPONDING FIELDS OF itab

FROM ( dfkkop INNER JOIN dimaiobpar ON dimaiobpar_{partner = dfkkop}gpart AND dimaiobpar_{insobject = dfkkop}vtref

INNER JOIN dimabroker ON dimabroker_{partner = dfkkop}gpart AND dimabroker_{insobject = dfkkop}vtref

INNER JOIN but020 ON but020_{partner = dfkkop}gpart INNER JOIN adrc ON adrc_{addrnumber = but020}addrnumber

INNER JOIN t001 ON t001_{bukrs = dfkkop}bukrs )

WHERE dimaiobpar~zstatus NE '1'

AND dfkkop~betrh NE '0'

AND dfkkop~gpart IN sel002

AND dfkkop~vtref IN sel003

AND but020~xdfadr = 'X'

GROUP BY dimaiobpar_{zgst dimaiobpar}zstatus dfkkop_{bukrs dfkkop}gpart dfkkop_{vtref dimabroker}brostmtyp adrc_{name1 adrc}name2

adrc_{name3 dimaiobpar}zabrtyp dimaiobpar_{zvat dimaiobpar}azawe_x dfkkop_{opbel t001}butxt.

if sy-subrc = 0.

sort itab ascending.

endif.

reward if useful...