ABAP Keyword Documentation → ABAP  Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL  Read Accesses → SELECT → SELECT  result → SELECT  select_list → SELECT  col_spec
SELECT  aggregate
Other versions: 7.31  7.40  7.54
Syntax
... { AVG( [DISTINCT] col )
 MAX( [DISTINCT] colsql_exp )
 MIN( [DISTINCT] colsql_exp )
 SUM( [DISTINCT] colsql_exp )
 COUNT( DISTINCT colsql_exp )
 COUNT( * )
 COUNT(*) } ...
Effect
Aggregate expression for

Specifies
col_spec
for a column of the results set in theSELECT
list in theSELECT
statement, 
Specifies an operand in the
HAVING
clause.
In an aggregate expression, a column col
of a database table or view 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:

AVG( [DISTINCT] col )
col
in the results
set or in the current group. The data type of the column must be numeric. The data types DF16_RAW and DF34_RAW and 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. 
MAX( [DISTINCT] colsql_exp )
MIN( [DISTINCT] colsql_exp )
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). 
SUM( [DISTINCT] colsql_exp )
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). 
COUNT( DISTINCT colsql_exp )
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). 
COUNT( * )
COUNT( * )
 If
COUNT( * )
orCOUNT(*)
is specified in aSELECT
list with other columns or together with aGROUP BY
clause, the data type of the result is INT4 and no numbers greater than 2147483647 can be determined.
 If
COUNT( * )
orCOUNT(*)
is specified as the only column and theGROUP BY
clause is not specified, the internal data type of the result is INT8 and numbers up to +9223372036854775807 can be determined. The system fieldsydbcnt
is set to the value 1 in results outside of the value range of the typei
.
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 value greater than the value range of INT4 is expected for a standalone function COUNT( *
) or COUNT(*)
.
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
 The database platform determines whether an overflow occurs if the result of an aggregate function exceeds its value range. The overflow behavior of SQL expressions, on the other hand, is platformindependent. An aggregate expression with an SQL expression raises an exception on every platform in the case of an overflow, even if a corresponding result of the aggregate function would not raise an exception on every platform.

If the aggregate function
SUM
is used for columns of type DF16_DEC, we recommend using a target field of data typedecfloat34
to avoid overflows. 
If SQL expressions are specified as arguments of aggregate expressions, the syntax check is performed in a
strict mode, which handles the statement more strictly than the regular syntax check.
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 sbookfldate,
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