topic Re: help with group by in Application Development and Automation Discussions

help with group by

Former Member — Sun, 14 Oct 2007 13:53:15 GMT

i wont to use group by but i have eroor waht i doing wrong?

SELECT pernr perid orgeh

FROM /bic/azps_ods600

INTO CORRESPONDING FIELDS OF TABLE b_tab

WHERE /bic/zps_wbs_d = project

GROUP BY orgeh.

regards

Re: help with group by

Former Member — Sun, 14 Oct 2007 13:55:56 GMT

Hi,

Whats the error.

Re: help with group by

Former Member — Sun, 14 Oct 2007 14:03:03 GMT

hi raghu

The field "/BIC/AZPS_ODS600~orgeh" from the SELECT list is is missing in the GROUP BY clause. is missing in the GROUP BY clause. is

thankes

Re: help with group by

Former Member — Sun, 14 Oct 2007 14:29:19 GMT

Hi,

Make sure that the field name orgeh is present as it is in the table /bic/azps_ods600 and is not an alias name as Group By does not consider the aliases.

Try writing the filed name orgeh in Upper case letters.f this does not work,then do send the eact error code that is being dsiplayed there.

The error that you have mentoned above is not clear enough.

Also,try debugging and find out whether the value for the field orgeh is valid in the table /bic/azps_ods600.

In case you have any further clarifications,do let me know.

Regards,

Puneet Jhari.

Re: help with group by

Former Member — Sun, 14 Oct 2007 16:54:33 GMT

maybe some one can give example how to do a group by for select into table

Thankes

Re: help with group by

Former Member — Sun, 14 Oct 2007 17:06:28 GMT

See the example group by statement :

select matnr shkzg meins sum( fklmg )

into table i_billed

from ( vbrk as k INNER JOIN vbrp as p on k_{vbeln = p}vbeln )

where ( vbtyp = 'M' or "invoice

vbtyp = 'N' ) and "invoice cancellation

k~fkdat in so_vdatu and

matnr in so_matnr and

p~werks = p_werks

group by matnr shkzg meins.

Refer above code and do it accordingly

Thanks

Seshu

Re: help with group by

Former Member — Sun, 14 Oct 2007 17:06:39 GMT

Hi,

Chk this link:

http://www.sts.tu-harburg.de/teaching/sap_r3/ABAP4/groupby_.htm

Also Eg:

DATA: count TYPE I, sum TYPE P DECIMALS 2, avg TYPE F.

DATA: connid TYPE sbook-connid.

SELECT connid COUNT( * ) SUM( luggweight ) AVG( luggweight )

INTO (connid, count, sum, avg)

FROM sbook

WHERE

carrid = 'LH' AND

fldate = '20010228'

GROUP BY connid.

WRITE: / connid, count, sum, avg.

ENDSELECT.

Sri

Re: help with group by

Former Member — Sun, 14 Oct 2007 17:08:27 GMT

Here are some examples of SELECt with GROUP BY clause -

SELECT manager_name, AVG(salary) AS average

FROM employees AS e JOIN managers AS m

ON e.manager_id = m.manager_id

WHERE manager_name IN ('Paul Smith', 'Mary Jones', 'John Miles')

GROUP BY manager_name

Output all Lufthansa departure points with the number of destinations:

TABLES: SPFLI.

DATA: BEGIN OF WA.

INCLUDE STRUCTURE SPFLI.

DATA: COUNT TYPE I.

DATA: END OF WA.

DATA: WA_TAB(72) TYPE C,

GTAB LIKE TABLE OF WA_TAB,

FTAB LIKE TABLE OF WA_TAB,

COUNT TYPE I.

CLEAR: GTAB, FTAB.

WA_TAB = 'CITYFROM COUNT( * ) AS COUNT'.

APPEND WA_TAB TO FTAB.

WA_TAB = 'CITYFROM'.

APPEND WA_TAB TO GTAB.

SELECT DISTINCT (FTAB)

INTO CORRESPONDING FIELDS OF WA

FROM SPFLI

WHERE

CARRID = 'LH'

GROUP BY (GTAB).

WRITE: / WA-CITYFROM, WA-COUNT.

ENDSELECT.

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.

(Instead of ENDSELECT you can use INTO table clause.)

Hope this helps.

ashish

Re: help with group by

Former Member — Mon, 15 Oct 2007 00:36:03 GMT

Hi,

Let's say you have 3 fields in custom database table:

MATNR, WERKS, KMENG.

and the values are:

100, 1000, 50.00

100, 1001, 80.00

100, 1001, 60.00

101, 1005, 100.00.

Now you can use group by to get the average of quantities (MENGE) for a given material and plant.


select MATNR WERKS AVG (MENGE)
into ITAB
from ZTABLE
group by MATNR WERKS.

Now the ITAB contents are :

100, 1000, 50.00

100, 1001, 70.00

101, 1005, 100.00

Regards,

Ferry Lianto