ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP - Database Accesses →  Open SQL →  Open SQL - Read Accesses →  SELECT →  SELECT - result → 

SELECT - aggregate

Short Reference

Syntax

... { MAX( [DISTINCT] col )
    | MIN( [DISTINCT] col )
    | AVG( [DISTINCT] col )
    | SUM( [DISTINCT] col )
    | COUNT( DISTINCT col )
    | COUNT( * )
    | COUNT(*) } ... .


Effect

As many of the specified column labels as you like can be listed in the SELECT command as arguments of the above aggregate expression. In aggregate expressions, a single value is calculated from the values of multiple rows in a column as follows (note that the addition DISTINCT excludes double values from the calculation):

  1. MAX( [DISTINCT] col ) Determines the maximum value of the value in the column col in the resulting set or in the current group.

  2. MIN( [DISTINCT] col ) Determines the minimum value of the content of the column col in the resulting set or in the current group.

  3. AVG( [DISTINCT] col ) Determines the average value of the content of the column col in the resulting set or in the current group. The data type of the column has to be numerical. The data types DF16_RAW, DF16_SCL, DF34_RAW, and DF34_SCL are not allowed.

  4. SUM( [DISTINCT] col ) Determines the sum of the content of the column col in the resulting set or in the current group. The data type of the column has to be numerical. The data types DF16_RAW, DF16_SCL, DF34_RAW, and DF34_SCL are not allowed.

  5. COUNT( DISTINCT col ) Determines the number of different values in the column col in the resulting set or in the current group.

  6. COUNT( * ) (or COUNT(*)) Determines the number of rows in the resulting set or in the current group. No column label is specified in this case.

If you are using aggregate expressions, all column labels that are not listed as an argument of an aggregate function are listed after the addition GROUP BY. The aggregate functions evaluate the content of the groups defined by GROUP BY in the database system and transfer the result to the combined rows of the resulting set.

The data type of aggregate expressions with the function MAX, MIN or SUM is the data type of the corresponding column in the ABAP Dictionary. Aggregate expressions with the function AVG for decimal floating point numbers have the corresponding data type (DF16_DEC or DF34_DEC); otherwise, they have the data type FLTP and those with COUNT have the data type INT4. The corresponding data object after INTO or APPENDING has to be selected accordingly. If the value of an aggregation expression is too large for the target area, an exception is raised. For the COUNT function in particular, numbers larger than 2,147,483,647 are not permitted.

Note the following points when using aggregate expressions:

Note

If the aggregate function SUM for columns of the type DF16_DEC is used, we recommend using a target field of data type decfloat34 to avoid overflows.

Example

Determine the number of airlines flying to New York.

DATA count TYPE i.

SELECT COUNT( DISTINCT carrid )
       FROM spfli
       INTO count
       WHERE cityto = 'NEW YORK'.

Example

Display the flight date, the number of passengers, the average and the maximum luggage weight of all Lufthansa flights with the flight number 0400.

DATA: fldate LIKE sbook-fldate,
      count  TYPE i,
      avg    TYPE p DECIMALS 2,
      max    TYPE p DECIMALS 2.

SELECT fldate COUNT( * ) AVG( luggweight ) MAX( luggweight )
       FROM sbook
       INTO (fldate, count, avg, max)
       WHERE carrid = 'LH' AND
             connid = '0400'
       GROUP BY fldate.
  WRITE: / fldate, count, avg, max.
ENDSELECT.