help in

[Former Member]

hi

i dont sucsess to do a group by i wont with group by to save the use of the collect

and do the same only in the select what is the best way to do that?

SELECT  /bic/zps_vr_ds  /bic/zps_ac_wk  /bic/zps_ev_hr
  FROM /bic/azps_ods600
  INTO CORRESPONDING FIELDS OF TABLE b_tab
  WHERE /bic/zps_wbs_d = project.

this is the collect

" i wont to save this collect

 LOOP AT b_tab.

    MOVE:  b_tab-/bic/zps_vr_ds TO inf_tab-/bic/zps_vr_ds,
           b_tab-/bic/zps_ac_wk TO inf_tab-/bic/zps_ac_wk,
           b_tab-/bic/zps_ev_hr TO inf_tab-/bic/zps_ev_hr.

    COLLECT inf_tab.
  ENDLOOP.

regards

[andreas_mann3]

hi look for agregate function in abap docu for command select:

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.

regards

A.

[Former Member]

Hi

<b>Variants:</b>

1. ... GROUP BY f1 ... fn

2. ... GROUP BY (source_text)

<b>... GROUP BY f1 ... fn</b>

Groups database table data in a SELECT command on one line in the result set. A group is a set of lines which all have the same values in each column determined by the field descriptors f1 ... fn.

... GROUP BY f1 ... fn always requires a list in the SELECT clause. If you use field descriptors without an aggregate funciton in the SELECTclause, you must list them in the GROUP BY f1 ... fn clause.

<b>Example</b> Output the number of passengers, the total weight and the average weight of luggage for all Lufthansa flights on 28.02.2001:

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.

... GROUP BY f1 ... fn is not supported for pooled and cluster tables.

The columns f1, ..., fn must not be of the type STRING or RAWSTRING.

<b>... GROUP BY (source_text)</b>

Works like GROUP BY f1 ... fn if the variable source_text contains the list f1 ... fn as ABAP source code.

The same restrictions apply to this variant as to GROUP BY f1 ... fn.

<b>Example</b> Output all Lufthansa departure points with the number of destinations:

DATA: BEGIN OF wa.

INCLUDE STRUCTURE spfli.

DATA: count TYPE I.

DATA: END OF wa.

DATA: field_list TYPE STRING,

group_by_list TYPE STRING,

count TYPE I.

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

group_by_list = 'CITYFROM'.

SELECT DISTINCT (field_list)

INTO CORRESPONDING FIELDS OF wa

FROM spfli

WHERE

carrid = 'LH'

GROUP BY (group_by_list).

WRITE: / wa-cityfrom, wa-count.

ENDSELECT.

<b>Performance:</b>

If aggregates and groups are formed by the database system and not just by the application server, this helps to reduce considerably the

<b>Reward if usefull</b>

[Former Member]

The GROUP BY clause summarizes several lines from the database table into a single line of the selection.

The GROUP BY clause allows you to summarize lines that have the same content in particular columns. Aggregate functions are applied to the other columns. You can specify the columns in the GROUP BY clause either statically or dynamically.

Specifying Columns Statically

To specify the columns in the GROUP BY clause statically, use:

SELECT <lines> <s1> [AS <a 1>] <s 2> [AS <a 2>] ...

<agg> <sm> [AS <a m>] <agg> <s n> [AS <a n>] ...

...

GROUP BY <s1> <s 2> ....

To use the GROUP BY clause, you must specify all of the relevant columns in the SELECT clause. In the GROUP BY clause, you list the field names of the columns whose contents must be the same. You can only use the field names as they appear in the database table. Alias names from the SELECT clause are not allowed.

All columns of the SELECT clause that are not listed in the GROUP BY clause must be included in aggregate functions. This defines how the contents of these columns is calculated when the lines are summarized.

Specifying Columns Dynamically

To specify the columns in the GROUP BY clause dynamically, use:

... GROUP BY (<itab>) ...

where <itab> is an internal table with line type C and maximum length 72 characters containing the column names <s 1 > <s 2 > .....

DATA: CARRID TYPE SFLIGHT-CARRID,

MINIMUM TYPE P DECIMALS 2,

MAXIMUM TYPE P DECIMALS 2.

SELECT CARRID MIN( PRICE ) MAX( PRICE )

INTO (CARRID, MINIMUM, MAXIMUM)

FROM SFLIGHT

GROUP BY CARRID.

WRITE: / CARRID, MINIMUM, MAXIMUM.

ENDSELECT.

[ferry_lianto]

Hi,

Please try this.

SELECT SUM( /bic/zps_vr_ds ) SUM( /bic/zps_ac_wk ) 
       SUM( /bic/zps_ev_hr )
FROM /bic/azps_ods600
INTO TABLE b_tab
WHERE /bic/zps_wbs_d = project
GROUP BY <fieldname>.

Regards,

Ferry Lianto