topic Re: Select statement for summing values in column? in Application Development and Automation Discussions

Select statement for summing values in column?

Former Member — Tue, 25 Nov 2008 09:16:42 GMT

How to write a select statement for sap standard table which contains data as given below

col1 col2

A 10

A 20

A 30

B 50

B 60

C 70

Need the output of select in internal table as given below

A 60

B 110

C 70

Regards,

Rachel

Re: Select statement for summing values in column?

Former Member — Tue, 25 Nov 2008 09:19:51 GMT

Hi,

You can use collect statement or u can use AT END OF field with the loop.

let me know if u have any issue in using these.

Regards,

Kusuma.

Re: Select statement for summing values in column?

Former Member — Tue, 25 Nov 2008 09:21:31 GMT

Hi Rachel,

You can use COLLECT statement( go through the key word documentation)

or while looping you can use Control break statements

AT NEW (( go through the key word documentation)

ENDAT.

Regards

Ramchander Rao.K

Re: Select statement for summing values in column?

Former Member — Tue, 25 Nov 2008 09:22:06 GMT

If you want to directly get sum from the standard table;

See F1 help for SELECT SUM()

If you can do it after fetching data into internal table;

See F1 help for COLLECT.

Do search the forum before posting a question. You will get number of thread in COLLECT and SELECT.

Regards

Karthik D

Re: Select statement for summing values in column?

Former Member — Tue, 25 Nov 2008 09:23:45 GMT

<copy&paste_removed_by_moderator>

Edited by: Julius Bussche on Nov 25, 2008 10:53 AM

Re: Select statement for summing values in column?

Former Member — Tue, 25 Nov 2008 09:37:48 GMT

@Jay

Nice copy & paste work from;

http://help.sap.com/saphelp_nw70/helpdata/en/fc/eb36d5358411d1829f0000e829fbfe/content.htm

Read the [Rules of Engagement|https://www.sdn.sap.com/irj/sdn/wiki?path=/display/home/rulesofEngagement] before posting more.

Regards

Karthik D

Re: Select statement for summing values in column?

Former Member — Tue, 25 Nov 2008 09:45:23 GMT

Within the loop COLLECT INTO a work area.

LOOP AT int_final INTO wa_final.

wa_final2-field1 = wa_final-field1.

wa_final2-field2 = wa_final-field2.

COLLECT wa_final2 INTO int_final2.

ENDLOOP.

Re: Select statement for summing values in column?

rainer_hbenthal — Tue, 25 Nov 2008 09:46:49 GMT


select col1, sum(col2) into <your_own_variables_here> from <tablename> group by col1.

Re: Select statement for summing values in column?

Former Member — Tue, 25 Nov 2008 09:47:24 GMT

suppose i_tab1 contains the data. the sum data is in i_tab2. Just do

define i_tab2 like

data: i_tab2 type standard table of ty_tab2 with key column1.

   loop at i_tab1 into w_tab1.
      collect w_tab1 into i_tab2.
   endloop.

Re: Select statement for summing values in column?

Former Member — Tue, 25 Nov 2008 10:45:20 GMT

Thanks for all the valuable input.

Below I have used sum in select statement(check case 2"),but i am not getting required o/p.

Kindly guide me.

case1 :

select a_{ebeln a}ebelp b_{packno b}menge

into corresponding fields of table itab

from ( eslh as a inner join esll as b

on b_{packno = a}packno )

where a~ebeln = 6000000100

and a~ebelp = 00010

and a~packno = '0000001025'

order by a_{ebeln a}ebelp b~packno .

O/p for the above .

ebeln ebelp packno menge

6000000100 00010 0000001025 6.000

6000000100 00010 0000001025 5.000

6000000100 00010 0000001025 2.000

6000000100 00010 0000001025 1.000

6000000100 00010 0000001025 2.000

case2:

select a_{ebeln a}ebelp b_{packno sum( b}menge )

into corresponding fields of table itab

from ( eslh as a inner join esll as b

on b_{packno = a}packno )

where a~ebeln = 6000000100

and a~ebelp = 00010

and a~packno = '0000001025'

group by a_{ebeln a}ebelp b~packno

order by a_{ebeln a}ebelp b~packno .

6000000100 00010 0000001025 0.000

Edited by: Rachel on Nov 25, 2008 11:45 AM

Edited by: Rachel on Nov 25, 2008 11:46 AM

Re: Select statement for summing values in column?

Former Member — Tue, 25 Nov 2008 10:54:28 GMT

Hi,

Use the collect statement.

COLLECT wa INTO itab

Thanks & Regards

Re: Select statement for summing values in column?

rainer_hbenthal — Tue, 25 Nov 2008 10:59:50 GMT

Just read my solution.