10-14-2007 2:53 PM
hi
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
10-15-2007 1:36 AM
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
10-14-2007 2:55 PM
10-14-2007 3:03 PM
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
10-14-2007 3:29 PM
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.
10-14-2007 5:54 PM
hi
maybe some one can give example how to do a group by for select into table
Thankes
10-14-2007 6:06 PM
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 kvbeln = pvbeln )
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
10-14-2007 6:06 PM
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
10-14-2007 6:08 PM
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
10-15-2007 1:36 AM
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