distinct and sum

[Former Member]

Hi experts

I am using the following query, i want to select the unique records only(BELNR) from the table.

how to change the query, pls advise me.

At the same time

Can we sum the value of DMBTR and then select the unique records.

Is it possible in a single query

SELECT BELNR

VALUT

EBELN

EBELP

LIFNR

MATNR

WERKS

MENGE

MEINS

GJAHR

DMBTR

FROM BSEG

INTO CORRESPONDING FIELDS OF TABLE IT_BSEG1

for all entries in it_bseg2

WHERE EBELN = IT_BSEG2-EBELN and

GJAHR = IT_BSEG2-GJAHR.

Regards

Rajaram

[Former Member]

Hi experts

I am using the following query, i want to select the unique records only(BELNR) from the table.

how to change the query, pls advise me.

At the same time

Can we sum the value of DMBTR and then select the unique records.

Is it possible in a single query

SELECT BELNR

VALUT

EBELN

EBELP

LIFNR

MATNR

WERKS

MENGE

MEINS

GJAHR

DMBTR

FROM BSEG

INTO CORRESPONDING FIELDS OF TABLE IT_BSEG1

for all entries in it_bseg2

WHERE EBELN = IT_BSEG2-EBELN and

GJAHR = IT_BSEG2-GJAHR.

Regards

Rajaram

[Former Member]

hI,

After the Seelct ..

SORT it_bseg2 by BELNR.

Delete adjacent duplicates from it_bseg2 comparing BELNR.

You can do the Sum, for this you need to use the Control break statments

Regards

Sudheer

[Former Member]

for getting sum decalre one more variable in second internal table

after that

sort it_bseg1 by belnr.

loop at it_bseg1.

if var1 is initial.

sum = sum + it_bseg1-dbmtr.

it_bseg2 = it_bseg1.

endif

var1 = it_bseg1-belnr.

sum = sum + it_bseg1-dbmtr.

if var1 ne it_bseg1-belnr.

it_bseg2 = it_bseg1.

it_bseg2 - sum = sum.

clear sum.

endif.

append it_bseg2.

clear it_bseg1,it_bseg2.

endloop.

Message was edited by:

KIRAN KUMAR

[Former Member]

xHi Raja,

Check this code which will have unique BELNRs and sum of DMBTR in this query..

SELECT <b>DISTINCT BELNR</b>

VALUT

EBELN

EBELP

LIFNR

MATNR

WERKS

MENGE

MEINS

GJAHR

<b>SUM( DMBTR )</b>

FROM BSEG

<b>GROUP BY VALUT EBELN EBELP LIFNR MATNR WERKS MENGE MEINS GJAHR</b>

INTO CORRESPONDING FIELDS OF TABLE IT_BSEG1

for all entries in it_bseg2

WHERE EBELN = IT_BSEG2-EBELN and

GJAHR = IT_BSEG2-GJAHR.

Thanks,

Vinay

[Former Member]

hi,

To read a several entries from the database, use the following:

SELECT is then empty), the system reads all of the lines that satisfy the WHERE condition. If you use DISTINCT, the system excludes duplicate entries.

SELECT  DISTINCT BELNR
VALUT
EBELN
EBELP
LIFNR
MATNR
WERKS
MENGE
MEINS
GJAHR
DMBTR
FROM BSEG
INTO CORRESPONDING FIELDS OF TABLE IT_BSEG1
for all entries in it_bseg2
WHERE EBELN = IT_BSEG2-EBELN and
GJAHR = IT_BSEG2-GJAHR.

To read aggregate data for a column in the database, use the following:

SELECT <lines> <agg>( [DISTINCT] <s1> ) [AS <a 1>] 
               <agg>( [DISTINCT] <s2> ) [AS <a 2>] ...

where .

· COUNT( * ) returns the total number of lines in the selection.

You can exclude duplicate values from the calculation using the DISTINCT option. The spaces between the parentheses and the arguments of the aggregate expressions must not be left out. The arithmetic operators AVG and SUM only work with numeric fields.

for more information follow this link.........

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3990358411d1829f0000e829fbfe/content.htm

regards,

Ashok Reddy

[former_member189059]

This should work

data: it_bseg1 type bseg occurs 0 with header line.
data: it_bseg2 type bseg occurs 0 with header line.
data: it_bseg1_old like line of it_bseg1.
DATA old_belnr LIKE it_bseg1-belnr.

DATA LV_BSEG1_MBTR_OLD LIKE IT_BSEG2-DMBTR.
DATA LV_FIRST VALUE 'X'.



SELECT belnr
valut
ebeln
ebelp
lifnr
matnr
werks
menge
meins
gjahr
dmbtr
FROM bseg
INTO CORRESPONDING FIELDS OF it_bseg1
FOR ALL ENTRIES IN it_bseg2
WHERE ebeln = it_bseg2-ebeln AND
gjahr = it_bseg2-gjahr.

IF it_bseg1_OLD-BELNR <> IT_bseg1-BELNR and lv_first = ''.
  APPEND it_bseg1_OLD to it_bseg1.
  CLEAR it_bseg1_OLD.
ELSE.
  it_bseg1_OLD = it_bseg1.
  it_bseg1_OLD-DMBTR = it_bseg1_OLD-DMBTR + LV_BSEG1_MBTR_OLD.
  LV_BSEG1_MBTR_OLD = it_bseg1_OLD-DMBTR.
ENDIF.

LV_FIRST = ''.
ENDSELECT.
APPEND it_bseg1_OLD to it_bseg2. " once more since we always append the second last value

Message was edited by:

Kris Donald