use of group by and having clause

[Former Member]

hi frnds

can anybody explain me the use of group by an having clause in select state ment

[former_member184569]

Hi Rohit,

1. GROUP BY f1 ... fn

2. GROUP BY (itab)

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 SELECT clause, 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.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.

Note

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

2. GROUP BY (itab)

Effect

Works like GROUP BY f1 ... fn if the internal table itab contains the list f1 ... fn as ABAP source code. The internal table itab can only have one field. This field must be of the type C and should not be more than 72 characters long. itab must be enclosed in parentheses and there should be no blanks between the parentheses and the table name.

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:

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 = 'COTYFROM COUNT( * ) AS COUNT'. APPEND FTAB.

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.

Regards,

Susmitha

[Former Member]

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.

You can only use the HAVING clause in conjunction with the GROUP BY clause.

To select line groups, use:

SELECT <lines> <s1> [AS <a1>] <s2> [AS <a2>] ...

<agg> <sm> [AS <am>] <agg> <sn> [AS <an>] ...

...

GROUP BY <s1> <s2> ....

HAVING <cond>.

The conditions <cond> that you can use in the HAVING clause are the same as those in the SELECT clause, with the restrictions that you can only use columns from the SELECT clause, and not all of the columns from the database tables in the FROM clause. If you use an invalid column, a runtime error results.

On the other hand, you can enter aggregate expressions for all columns read from the database table that do not appear in the GROUP BY clause. This means that you can use aggregate expressions, even if they do not appear in the SELECT clause. You cannot use aggregate expressions in the conditions in the WHERE clause.

As in the WHERE clause, you can specify the conditions in the HAVING clause as the contents of an internal table with line type C and length 72.

http://help.sap.com/saphelp_erp2005/helpdata/en/fc/eb3000358411d1829f0000e829fbfe/frameset.htm

Thanks and Regards,

Bharat Kumar Reddy.V