ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL  Operands and Expressions → ABAP SQL  SQL Expressions sql_exp → sql_exp  sql_agg
sql_exp  agg_func
Other versions:
7.31  7.40  7.54
Syntax
... AVG( [DISTINCT]
col [AS dtype] )
 MAX( [DISTINCT] sql_exp )
 MIN( [DISTINCT] sql_exp )
 SUM( [DISTINCT] sql_exp )
 PRODUCT( col )
 STRING_AGG(
sql_exp[, sep] [ORDER BY
col1 [ASCENDINGDESCENDING],
col2 [ASCENDINGDESCENDING], ...] )
 COUNT( [DISTINCT] sql_exp )
 COUNT( * )
 COUNT(*)

GROUPING( col ) ...
Variants
1. ... AVG( [DISTINCT] col [AS dtype] )
2. ... MAX( [DISTINCT] sql_exp )
3. ... MIN( [DISTINCT] sql_exp )
4. ... SUM( [DISTINCT] sql_exp )
5. ... PRODUCT( col )
6. ... STRING_AGG( sql_exp[, sep] [ORDER BY ...] )
7. ... COUNT( [DISTINCT] sql_exp )
8. ... COUNT( * )
9. ... COUNT( * )
10. ... GROUPING( col )
Effect
Aggregate function in ABAP SQL. An aggregate function aggregates the values of the rows in a specific set of rows to a single value. Aggregate functions can be used in the following places:

As a standalone aggregate
expression
agg_exp
in specific operand positions of a query. The evaluated row set is the full results set of the query or a group created using the additionGROUP BY
. The general rules apply. 
As a window function
win_func
in a window expression. The evaluated row set is the current window or a box inside the window. The general rules apply, plus the rules for window functions. 
As an aggregate function in a hierarchy aggregate navigator. The evaluated row set consists of the
hierarchy nodes determined by the navigator. The general rules apply, plus the rules for hierarchy aggregate navigators.
The following table shows which aggregate functions can be used in which places:
  agg_exp  win_exp  HIERARCHY_DESCENDANTS_AGGREGATE  HIERARCHY_ANCESTORS_AGGREGATE 

AVG 
x  x     
MAX 
x  x  x  x 
MIN 
x  x  x  x 
SUM 
x  x  x  x 
PRODUCT 
      x 
STRING_AGG 
x      x 
COUNT 
x  x  x  x 
COUNT(*) 
x  x  x  x 
GROUPING 
x       
The following shared properties apply here:

The addition
DISTINCT
excludes duplicate values from the calculation in any operand positions in which it is specified. 
If the argument of an aggregate function (except
COUNT
) 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. 
The aggregate function
COUNT
counts rows and never produces the null value. 
Arguments of type STRING, RAWSTRING, LCHR, LRAW, and GEOM_EWKB cannot be handled using aggregate functions.
Note
The database platform determines whether an overflow occurs if the result of an aggregate function exceeds its value range. On some database platforms, intermediate results outside the value range are allowed. The overflow behavior of SQL expressions, on the other hand, is platformindependent. If an SQL expression in an aggregate expression produces an overflow, an exception is raised on every platform, even if a corresponding result of the aggregate function would not raise an exception on every platform.
Variant 1
... AVG( [DISTINCT] col [AS dtype] )
Effect
Determines the average value of the content of a column
col in a row set. The optional addition AS dtype
can be used to define the result type explicitly.
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 INT8 is only permitted in conjunction with the addition AS dtype
.
The implicit data type of the result for
decimal floating point numbers is the corresponding data type (DECFLOAT16 or DECFLOAT34 (or DF16_DEC
or DF34_DEC)) and is otherwise the type FLTP, a platformdependent subtotal, or is determined by the addition AS dtype
.
SQL expressions cannot be specified as arguments for AVG
.
Note
The result of the aggregate functions AVG
in the data type FLTP is platformdependent.
The type of platform also determines whether the result of an aggregate expression AVG
,
which is used in a HAVING
clause as a subtotal, is of type FLTP. The addition
AS dtype
can be used to force the type FLTP.
Example
Determines the average value of all values in a column and checks the result.
DATA(n) = 9.
DELETE FROM demo_expressions.
INSERT demo_expressions FROM TABLE @( VALUE #(
FOR i = 0 UNTIL i > n
( id = CONV #( i ) num1 = i ) ) ).
SELECT AVG( num1 )
FROM demo_expressions
INTO (@DATA(avg)).
ASSERT avg = n / 2 .
Variant 2
... MAX( [DISTINCT] sql_exp )
Variant 3
... MIN( [DISTINCT] sql_exp )
Effect
Determines the maximum value or minimum value of the results of the SQL expression sql_exp
in a row set. The data type of the result is the
external data type of the result of the SQL expression.
The result of the SQL expression sql_exp
must be a numeric type (except for the
replacement types for decimal floating point
numbers). If a single column col
is specified for the expression, this column can also have any data type.
Example
Determines the minimum value and maximum value of all values in a column and checks the result.
DATA(n) = 9.
DELETE FROM demo_expressions.
INSERT demo_expressions FROM TABLE @( VALUE #(
FOR i = 0 UNTIL i > n
( id = CONV #( i ) num1 = i ) ) ).
SELECT MIN( num1 ) AS min, MAX( num1 ) AS max
FROM demo_expressions
INTO (@DATA(min),@DATA(max)).
ASSERT min = 0.
ASSERT max = n.
Variant 4
... SUM( [DISTINCT] sql_exp )
Effect
Determines the sum of the results of the SQL expression sql_exp
in a row set. The data type of the result is the
external data type of the result of the SQL expression.
The result of the SQL expression sql_exp
must be a numeric type (except for the
replacement types for decimal floating point
numbers). If a single column col
is specified for the expression, this column can have any numeric data type except DF16_RAW and DF34_RAW and except the obsolete data types DF16_SCL and DF34_SCL .
Note
If the aggregate function SUM
is used for columns of types DECFLOAT16 or DF16_DEC, it is best to use a target field with the data type decfloat34
to avoid overflows.
Example
Totals all values in a column and checks the result.
DATA(n) = 9.
DELETE FROM demo_expressions.
INSERT demo_expressions FROM TABLE @( VALUE #(
FOR i = 0 UNTIL i > n
( id = CONV #( i ) num1 = i ) ) ).
SELECT SUM( num1 )
FROM demo_expressions
INTO (@DATA(sum)).
ASSERT sum = ( n * ( n + 1 ) ) / 2.
Variant 5
... PRODUCT( col )
Effect
Determines the product of the values of a column col
in a row set. The data type of the result is the
external data type of the column. The column must have a numeric type (except for the
replacement types for decimal floating point numbers).
Note
The aggregate function PRODUCT
can currently be used only in the
hierarchy aggregate
navigator HIERARCHY_ANCESTORS_AGGREGATE
.
Executable Example
See Hierarchy Navigator HIERARCHY_ANCESTORS_AGGREGATE
Variant 6
... STRING_AGG( sql_exp[, sep] [ORDER BY ...] )
Effect
Chains the results of the SQL expression sql_exp
in a row set. The data type of the result is SSTRING with the length 1333. If the string in question
is greater then 1333, a handleable exception of the class CX_SY_OPEN_SQL_DB is raised The results of
the SQL expression sql_exp
must be a flat characterlike data type like CHAR, SSTRING, DATS, or TIMS.
An optional separator sep
separated by a comma can be specified after the
SQL expression. If sep
is specified, its content is inserted into the string
between successive results of the SQL expression. If sep
is not specified, these results are appended to each other directly. sep
can be a
literal or a
host constant with the
ABAP type c
,
d, t
, n
, or string
with a maximum of 1333 characters.
The optional addition ORDER BY
can be used to define the order in which the
results of the SQL expression are chained. If ORDER BY
is not specified,
the order is undefined. ORDER BY
is followed by a commaseparated list of
columns of the data sources of the current query. These columns are used to sort the rows of the row
set. A column can only be specified directly using the column name
col1
, col2
... Alias names defined using AS
cannot be specified. The additions ASCENDING
and DESCENDING
determine whether the rows are sorted in ascending or descending order by the column in question. The default is ASCENDING
.
Note

The addition
DISTINCT
cannot be specified for the aggregate functionSTRING_AGG
. 
The aggregate function
STRING_AGG
cannot be used as a window function. 
When used, the aggregate function
STRING_AGG
requires the strict mode from Release 7.54.
Executable Example
SQL Expressions, Aggregate Function string_agg
Variant 7
... COUNT( [DISTINCT] sql_exp )
Effect
Determines the number of distinct values of the results of the SQL expression sql_exp
in a row set.
 If the addition
DISTINCT
is not specified,COUNT
determines all rows in which the result of the SQL expressionsql_exp
is not the null value
 If the addition
DISTINCT
is specified,COUNT
determines the number of distinct values of the results of the SQL expressionsql_exp
. Null values are ignored here.
The data type of the result is INT4. The SQL expression sql_exp
can be a result with any type (except for the
replacement types for decimal floating point
numbers). If a single column col
is specified for the expression, this column can have any data type.
Note
An aggregate function COUNT
without the addition DISTINCT
requires the strict mode from Release 7.54.
Example
Determines the number of airlines flying to New York.
SELECT COUNT( DISTINCT carrid )
FROM spfli
WHERE cityto = 'NEW YORK'
INTO @DATA(count).
Variant 8
... COUNT( * )
Variant 9
... COUNT( * )
Effect
The two spellings have the same meaning and, regardless of a specific value, produce the number of rows in a row set: The following applies to the data type of the result:

If used as an aggregate expression
agg_exp
:
 If
COUNT( * )
orCOUNT(*)
is specified as an aggregate expression 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 as an aggregate expression in aSELECT
list, the internal data type of the result isINT8
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 used as a window function
win_func
and in hierarchy aggregate navigators, the data type of the result is INT8.
Note
A target object of the type INT8
, p
, or decfloat34
must be specified if a standalone function COUNT( *
) or COUNT(*)
expects a value greater than the value range of INT4.
Example
Example for counting rows using COUNT
. The singlerow result contains the values 7, 2, and 10 in its columns.

10 is the total number of rows in the results set determined using
COUNT(*)
and is not dependent on a single value. 
7 is the number of rows determined using
COUNT
(withoutDISTINCT
) in which case distinction does not produce the null value. 
2 is the number of distinct results "X" and "Y” determined using
COUNT
undDISTINCT
of the case distinction while ignoring the null value.
The difference 3 of the results of COUNT(*)
and COUNT
without DISTINCT
is the number of rows in which the
case distinction produces the null value.
DELETE FROM demo_expressions.
INSERT demo_expressions
FROM TABLE @( VALUE #(
FOR i = 1 UNTIL i > 10 ( id = CONV #( i ) num1 = i ) ) ).
SELECT FROM demo_expressions
FIELDS
COUNT(
CASE WHEN num1 < 4 THEN 'X'
WHEN num1 BETWEEN 4 AND 7 THEN 'Y' END ) AS cnt,
COUNT( DISTINCT
CASE WHEN num1 < 4 THEN 'X'
WHEN num1 BETWEEN 4 AND 7 THEN 'Y' END ) AS cntdist,
COUNT(*) AS cntstar
INTO TABLE @DATA(result).
cl_demo_output=>display( result ).
Variant 10
... GROUPING( col )
Effect
The grouping function GROUPING
can be used to verify whether a column col
is part of the aggregation. The grouping function can be used only if the GROUP BY
clause contains the addition GROUPING SETS
. The data type of the result of the grouping function is
INT1.
SQL expressions cannot be specified as arguments for
GROUPING. More information can be found in sql_agg  GROUPING
.