topic Re: regarding select statement (max. and group by) in Application Development and Automation Discussions

regarding select statement (max. and group by)

Former Member — Tue, 02 Jan 2007 04:01:56 GMT

hi champions,

i need ur help ,i m having some problem in select max. and group by statement ,cud u plz explain this scenario with the help of eg...thnx in advance..

warm regards,

ravi.

Re: regarding select statement (max. and group by)

Former Member — Tue, 02 Jan 2007 04:04:54 GMT

Max is used to fetch the record having maximm value say for some particular column like select max ( field 1 ) ....

and for group by see F1 help on select ,i pasted the same below -

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.

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

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:

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.

Re: regarding select statement (max. and group by)

Former Member — Tue, 02 Jan 2007 04:15:57 GMT

Hi Ravi,

Check this post.

https://forums.sdn.sap.com/click.jspa?searchID=566912&messageID=1481350

https://forums.sdn.sap.com/click.jspa?searchID=566912&messageID=1910371

Regards,

Priyanka.

Re: regarding select statement (max. and group by)

Former Member — Tue, 02 Jan 2007 04:18:18 GMT

Hi,

<b>SELECT - group</b>

Syntax

... GROUP BY { {col1 col2 ...} | (column_syntax) } ... .

Effect

The addition GROUP BY combines groups of rows that have the same content in their specified columns col1 col2 ... in the resulting set into a single row.

The use of GROUP BY has the prerequisite that SELECT only individual columns, not all the columns, are specified using *. If GROUP BY is used, all columns that are specified directly after SELECT and not specified as an argument of an aggregate function must be listed there. Conversely, if GROUP BY is used, all the columns listed after SELECT that are not specified after GROUP BY must be specified as an argument of an aggregate function. The aggregate functions define how the content of these columns is determined in the combined row from the contents of all the rows of a group.

After GROUP BY, the same column identifiers must be specified as after SELECT. The specification can either be specified statically as a list col1 col2 ... or dynamically as a brackted data object column_syntax that - at execution of the statement - contains the syntax of the staticspecification or is set to initial value. For column_syntax, the same applies as for the dynamic column specification after SELECT.

If the content of column_syntax initial, either all the rows or no rows at all are grouped together. The columns after SELECT must then be listed either solely as arguments of aggregate functions or solely directly. If not, prior to Release 6.10 you could trigger a runtime error. As of Release 6.10, this would trigger an exception CX_SY_OPEN_SQL_DB that can be handled.

The columns listed after GROUP BY must not be of type STRING or RAWSTRING, and if GROUP BY is used, pool or cluster tables cannot be accessed.

<b>select-MAX</b> MAX( [DISTINCT] col ) Determines the maximum value of the value in the column col in the resulting set or in the current group.

regards,

pankaj singh

mark all helpful answers

Re: regarding select statement (max. and group by)

Former Member — Tue, 02 Jan 2007 04:24:47 GMT

If you use aggregate functions together with one or more database fields in the SELECT clause , you must also all the database fields not specified by one of the aggregate functions under GROUP BY fields.

To know more about GROUP BY,Just check out this link

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