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 [ASCENDING|DESCENDING], 

                 
                 
    col2 [ASCENDING|DESCENDING], ...] ) 

  | 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 addition GROUP 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 platform-independent. 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 platform-dependent 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 platform-dependent. 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 character-like 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 comma-separated 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 function STRING_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 expression sql_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 expression sql_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( * ) or COUNT(*) is specified as an aggregate expression in a SELECT list with other columns or together with a GROUP BY clause, the data type of the result is INT4 and no numbers greater than 2147483647 can be determined.

If COUNT( * ) or COUNT(*) is specified as the only column and the GROUP BY clause is not specified as an aggregate expression in a SELECT list, the internal data type of the result is INT8 and numbers up to +9223372036854775807 can be determined. The system field sy-dbcnt is set to the value -1 in results outside of the value range of the type i.

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 single-row 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 (without DISTINCT) in which case distinction does not produce the null value.
2 is the number of distinct results "X" and "Y” determined using COUNT und DISTINCT 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.

Executable Example

GROUPING, Aggregate Function

Continue

sql_agg - AVG, AS