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

help with group by

Former Member — Mon, 15 Oct 2007 07:47:06 GMT

hallow

i need help with group by statment

i try like that and i have erorr

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

GROUP BY /bic/zps_vr_ds.

the eroor is:

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

Re: help with group by

Former Member — Mon, 15 Oct 2007 07:50:17 GMT

you must always declare the field in you're selection on which you do the group by on. further on you have to define for the other field which value you want if more than 1 is possible through the group by.

SELECT /bic/zps_vr_ds max(/bic/zps_ac_wk) max(/bic/zps_ev_hr)

FROM /bic/azps_ods600

INTO CORRESPONDING FIELDS OF TABLE b_tab

WHERE /bic/zps_wbs_d = project

GROUP BY /bic/zps_vr_ds.

futher on I am not familiar with you're way of tables and fields so if there is an error in ther

Message was edited by:

A. de Smidt

Re: help with group by

Former Member — Mon, 15 Oct 2007 07:53:33 GMT

What exactly are you trying to do?

You use GROUP BY when you want to make use of aggregate operations eq min( ), max( ), sum ( ) etc.

eg select a b sum( c )

from mytable

group by a b

Each field in the select statement either needs to be listed in the GROUP BY clause, or have an aggregate function applied to it.

Re: help with group by

Former Member — Mon, 15 Oct 2007 08:05:19 GMT

good

pls check this code

SELECT result FROM source [INTO target] [WHERE condition] [GROUP BY fields] [ORDER BY order].

SELECT clause

Variants:

1. SELECT [SINGLE [FOR UPDATE] DISTINCT] *

2. SELECT [SINGLE [FOR UPDATE] DISTINCT] s1 ... sn

3. SELECT [SINGLE [FOR UPDATE] DISTINCT] (itab)

FROM clause

Variants:

1. ... FROM dbtab

Additions:

1. ... CLIENT SPECIFIED

2. ... BYPASSING BUFFER

3. ... UP TO n ROWS

2. ... FROM (dbtabname)

Additions:

1. ... CLIENT SPECIFIED

2. ... BYPASSING BUFFER

3. ... UP TO n ROWS

INTO target

(This form of the FROM clause works only in conjunction with the INTO clause.)

INTO clause

Variants:

1. ... INTO wa

2. ... INTO CORRESPONDING FIELDS OF wa

3. ... INTO (f1, ..., fn)

4. ... INTO TABLE itab

5. ... INTO CORRESPONDING FIELDS OF TABLE itab

6. ... APPENDING TABLE itab

7. ... APPENDING CORRESPONDING FIELDS OF TABLE itab

WHERE clause

Variants:

1. ... WHERE f op g

2. ... WHERE f [NOT] BETWEEN g1 AND g2

3. ... WHERE f [NOT] LIKE g

4. ... WHERE f [NOT] IN (g1, ..., gn)

5. ... WHERE f [NOT] IN itab

6. ... WHERE f IS [NOT] NULL

7. ... WHERE NOT cond

8. ... WHERE cond1 AND cond2

9. ... WHERE cond1 OR cond2

10. ... WHERE (itab)

11. ... WHERE cond AND (itab)

12. ... FOR ALL ENTRIES IN itab WHERE cond

Operator Meaning

EQ or = equal to

NE or < > not equal to

LT or < less than

LE or < = less than or equal to

GT or > greater than

GE or >= greater than or equal to

GROUP-BY clause

Variants:

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

2. ... GROUP BY (itab)

ORDER-BY clause

Variants:

1. ... ORDER BY PRIMARY KEY

2. ... ORDER BY f1 ... fn

3. ... ORDER BY (itab)

reward point if helpful.

thanks

mrutyun^

Re: help with group by

Former Member — Mon, 15 Oct 2007 08:07:50 GMT

Hi TAL,

Check this.

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.

eg 1:

... 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.

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.

Note:

... 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.

eg :2

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

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.

If aggregates and groups are formed by the database system and not just by the application server, this helps to reduce considerably the volume of data that has to be transported from the database server to the application server.

Reward if Useful,

Regards,

Chitra

Re: help with group by

Former Member — Mon, 15 Oct 2007 08:11:27 GMT

SELECT objid gjahr accrule acrtype effdate INTO CORRESPONDING FIELDS OF

TABLE parent_class->acepsoit_table_ FROM acepsoit

WHERE bukrs IN parent_class->company_code_range

AND acrtype IN parent_class->accrual_type_range

AND accrule IN parent_class->acct_principle_range

AND effdate IN parent_class->key_date_range

AND poyear IN parent_class->fiscal_yr_range

GROUP BY effdate objid gjahr accrule acrtype.

Re: help with group by

Former Member — Mon, 15 Oct 2007 08:12:19 GMT

Variants:

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

2. ... GROUP BY (source_text)

Variant 1

... GROUP BY f1 ... fn

Effect

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.

Example

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.

Notes

... 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.

Variant 2

... GROUP BY (source_text)

Effect

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

Note

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

Example

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.

Note

Performance:

Regards

Pavan