ABAP - Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Open SQL →  Open SQL - Read Accesses →  SELECT →  SELECT - select_clause →  SELECT - select_list →  SELECT - col_spec → 

SELECT - aggregate

Quick Reference

Syntax

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


Effect

Aggregate expression for

In an aggregate expression, a column col of a data source or an SQL expression sql_exp is specified as an argument of one of the aggregate functions shown here. Each aggregate function calculates a single value from the values of the column or from the results of the SQL expression and from multiple rows as follows. Here, the addition DISTINCT excludes duplicate values from the calculation:

Determines the average value of the contents of column col in the results set or in the current group. The data type of the column must be numeric. The data types INT8, DF16_RAW, and DF34_RAW plus the obsolete types DF16_SCL and DF34_SCL are not allowed. The data type of the result for decimal floating point numbers is the corresponding data type (DF16_DEC or DF34_DEC) and otherwise FLTP. SQL expressions cannot be specified as arguments.
Determine the maximum value or minimum value of the values of the column col or of the results of the SQL expression sql_exp in the results set or the current group. The data type of the result is the external data type of the corresponding column or of the result of the SQL expression. A column with any data type can be specified for col. The result of an SQL expression sql_exp must be a numeric type (except for the types for decimal floating point numbers).
Determines the total of the content of the column col or of the results of the SQL expression sql_exp in the results set or the current group. The data type of the result is the external data type of the corresponding column or of the result of the SQL expression. The data type of the column col must be a numeric type. The data types DF16_RAW and DF34_RAW and the obsolete types DF16_SCL and DF34_SCL are not allowed. The result of an SQL expression sql_exp must be a numeric type (except for the types for decimal floating point numbers).
Determines the number of distinct values in the column col or of the results of the SQL expression sql_exp in the results set or the current group. The data type of the result is INT4. A column with any data type can be specified for col. An SQL expression sql_exp can be a result with any type (except for the types for decimal floating point numbers).
Determines the number of rows in the results set or in the current group. No column identifier is specified in this case.

If the value of an aggregate expression is too large for the target area, an exception is raised. More specifically, a target object of the type p or decfloat34 must be specified if a standalone function COUNT( * ) or COUNT(*) expects a value greater than the value range of INT4.

If the argument of an aggregate function has the null value, it is ignored when the function is evaluated. The result is a null value only if all the rows in the column in question contain a null value.

Notes

Example

Determines the number of airlines flying to New York.

DATA count TYPE i.

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

Example

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

TYPES: BEGIN OF wa,
        fldate LIKE sbook-fldate,
        count  TYPE i,
        avg    TYPE p DECIMALS 2,
        max    TYPE p DECIMALS 2,
      END OF wa.
DATA  itab TYPE TABLE OF wa WITH EMPTY KEY.

SELECT fldate, COUNT( * ), AVG( luggweight ), MAX( luggweight )
       FROM sbook
       WHERE carrid = 'LH' AND
             connid = '0400'
       GROUP BY fldate
       INTO TABLE @itab.

cl_demo_output=>display( itab ).

Example

See SQL Expressions, Use in Aggregate Expressions .