GROUP-BY Clause

GROUP-BY clause

Variants:

1. ... GROUP BY f1 ... fn
2. ... GROUP BY (itab)

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.

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:   GTAB(72) OCCURS 5 WITH HEADER LINE, 
		FTAB(72) OCCURS 5 WITH HEADER LINE, 
		COUNT TYPE I. 
 
REFRESH: GTAB, FTAB. 
FTAB = 'CITYFROM COUNT( * ) AS COUNT'. APPEND FTAB. 
GTAB = 'CITYFROM'.					 APPEND GTAB. 
 
SELECT DISTINCT (FTAB) 
	 INTO CORRESPONDING FIELDS OF WA 
	 FROM SPFLI 
	 WHERE 
		 CARRID   = 'LH' 
	 GROUP BY (GTAB). 
  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.