SAP SQL AGG FUNC
Get Example source ABAP code based on a different SAP table
• STRING_AGG ABAP_OSQL_AGG_FUNC
• ORDER BY ABAP_OSQL_AGG_EXP
• MAX ABAP_OSQL_AGG_FUNC
• MIN ABAP_OSQL_AGG_FUNC
• AVG ABAP_OSQL_AGG_FUNC
• MEDIAN ABAP_OSQL_AGG_FUNC
• Standard SAP Help forDEV ABAP_OSQL_AGG_FUNC
• VAR ABAP_OSQL_AGG_FUNC
• CORR ABAP_OSQL_AGG_FUNC
• CORR_SPEARMAN ABAP_OSQL_AGG_FUNC
• SUM ABAP_OSQL_AGG_FUNC
• PRODUCT ABAP_OSQL_AGG_FUNC
• COUNT ABAP_OSQL_AGG_FUNC
• COUNT( * ) ABAP_OSQL_AGG_FUNC
• COUNT(*) ABAP_OSQL_AGG_FUNC
• DISTINCT ABAP_OSQL_AGG_FUNC
ABAP_SQLAGG - agg_func ABAP_SYNTAX ... AVG( $[DISTINCT$] col $[ AS dtype $] )
$| MEDIAN( $[DISTINCT$] sql_exp )
$| MAX( $[DISTINCT$] sql_exp )
$| MIN( $[DISTINCT$] sql_exp )
$| SUM( $[DISTINCT$] sql_exp )
$| PRODUCT( col )
$| Standard SAP Help forDEV( $[DISTINCT$] sql_exp )
$| VAR( $[DISTINCT$] sql_exp )
$| CORR( sql_exp1, sql_exp2 )
$| CORR_SPEARMAN( sql_exp1, sql_exp2 )
$| STRING_AGG( sql_exp$[, sep$] $[ORDER BY col1 $[ASCENDING$|DESCENDING$],
col2 $[ASCENDING$|DESCENDING$], ...$] )
$| COUNT( $[DISTINCT$] sql_exp )
$| COUNT( * )
$| COUNT(*)
$| GROUPING( col ) ...
$| ALLOW_PRECISION_LOSS( ... )
ABAP_VARIANTS:
1 ... AVG( $[DISTINCT$] col $[AS dtype$] )
2 ... MEDIAN( $[DISTINCT$] sql_exp )
3 ... MAX( $[DISTINCT$] sql_exp )
4 ... MIN( $[DISTINCT$] sql_exp )
5 ... SUM( $[DISTINCT$] sql_exp )
6 ... PRODUCT( col )
7 ... Standard SAP Help forDEV( $[DISTINCT$] sql_exp )
8 ... VAR( $[DISTINCT$] sql_exp )
9 ... CORR( sql_exp1,sql_exp2 )
10 ... CORR_SPEARMAN( sql_exp1,sql_exp2 )
11 ... STRING_AGG( sql_exp$[, sep$] $[ORDER BY ...$] )
12 ... COUNT( $[DISTINCT$] sql_exp )
13 ... COUNT( * )
14 ... COUNT(*)
15 ... GROUPING( col )
16 ... ALLOW_PRECISION_LOSS( ... )
What does it do?
Aggregate function in AB_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 result set of the query or a group created using the addition GROUP BY. The general rules described here 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 in addition to 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 in addition to 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
AVGxx--
MEDIANxx--
MAXxxxx
MINxxxx
SUMxxxx
PRODUCT---x
Standard SAP Help forDEVxx--
VARxx--
CORRxx--
CORR_SPEARMANxx--
STRING_AGGx--x
COUNTxxxx
COUNT(*)xxxx
GROUPINGx---
ALLOW_PRECISION_LOSSx---
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.
Latest notes:
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.
For further details on the calculation on SAP HANA Platform, see the SAP HANA SQL Reference Guide.
NON_V5_HINTS
ABAP_HINT_END
ABAP_VARIANT_1 ... AVG( $[DISTINCT$] col $[AS dtype$] )
What does it do?
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
BEGIN_SECTION VERSION 5 OUT and the obsolete types DF16_SCL and DF34_SCL
END_SECTION VERSION 5 OUT are not allowed. The data type INT8 is only allowed together 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 intermediate result, or is determined by the addition AS dtype .
SQL expressions cannot be specified as arguments for AVG.
Latest notes:
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 an intermediate result, is of type FLTP . The addition AS dtype can be used to force the type FLTP.
NON_V5_HINTS
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Determination of the average value of all values in a column and checking of the result.
ABEXA 01259
ABAP_EXAMPLE_END
ABAP_VARIANT_2 ... MEDIAN( $[DISTINCT$] sql_exp )
What does it do?
Determines the statistical median of an input expression. Null values are ignored. If the number of non-null values is even, then the return value is the average of the two middle elements. Otherwise, the middle element is returned. 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 have a numeric data type except for the replacement types for decimal floating point numbers.
ABAP_EXAMPLE_VX5
Determination of the median value of all values in a column. As the number of non-null values is even, the average of the two middle values is calculated. Since the data type of the result is INT4, the integer is rounded. The value returned is 6.
ABEXA 01435
ABAP_EXAMPLE_END
ABAP_VARIANT_3 ... MAX( $[DISTINCT$] sql_exp )
ABAP_VARIANT_4 ... MIN( $[DISTINCT$] sql_exp )
What does it do?
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.
ABAP_EXAMPLE_VX5
Determination of the minimum value and maximum value of all values in a column and checking of the result.
ABEXA 01260
ABAP_EXAMPLE_END
ABAP_VARIANT_5 ... SUM( $[DISTINCT$] sql_exp )
What does it do?
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
BEGIN_SECTION VERSION 5 OUT and except the obsolete data types DF16_SCL and DF34_SCL
END_SECTION VERSION 5 OUT .
Latest notes:
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.
NON_V5_HINTS
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Summation of all values in a column and checking the result.
ABEXA 01261
ABAP_EXAMPLE_END
ABAP_VARIANT_6 ... PRODUCT( col )
What does it do?
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.
Latest notes:
The aggregate function PRODUCT can currently be used only in the hierarchy aggregate navigator HIERARCHY_ANCESTORS_AGGREGATE.
NON_V5_HINTS
ABAP_HINT_END
ABAP_EXAMPLE_ABEXA
See Hierarchy Navigator HIERARCHY_ANCESTORS_AGGREGATE
ABAP_EXAMPLE_END
ABAP_VARIANT_7 ... Standard SAP Help forDEV( $[DISTINCT$] sql_exp )
What does it do?
Determines the standard deviation of a given expression as the square root of the VAR function. The result of the SQL expression sql_exp can have either the data type FLTP or DECFLOAT34. To use Standard SAP Help forDEV with other data types, a conversion must first be performed using the SQL function CAST. The data type of the result is the external data type of the result of the SQL expression, that is, either FLTP or DECFLOAT34.
See Variant 8 below for an example.
ABAP_VARIANT_8 ... VAR( $[DISTINCT$] sql_exp )
What does it do?
Determines the variance of a given expression as the square of the standard deviation. The SQL expression sql_exp can only be FLTP or DECFLOAT34 . To use VAR with other data types, a conversion must first be performed using the SQL function CAST . The data type of the result is the external data type of the result of the SQL expression, that is, either FLTP or DECFLOAT34.
ABAP_EXAMPLE_VX5
Determination of the average, the standard deviation, and the variance of the salaries of all employees listed in table DEMO_EMPLOYEES. The data type of column SALARY is DEC and to calculate the standard deviation and variance, the column is converted to type DECFLOAT34 .
ABEXA 01433
ABAP_EXAMPLE_END
ABAP_VARIANT_9 ... CORR( sql_exp1,sql_exp2 )
What does it do?
Determines the Pearson product-moment correlation coefficient between two columns. In other words, it measures the linear correlation of two value sets. The result of the SQL expressions sql_exp1 and sql_exp2 can have any numeric data type. The data type of the result is always FLTP . The result ranges from -1 to 1. If a correlation cannot be computed, the result is null.
See Variant 10 below for an example.
Latest notes:
If the aggregate function CORR is used as a window function, the ROWS BETWEEN addition is only supported with UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING. Otherwise, a syntax warning occurs, if detected at compile time, or a catchable exception of class CX_SY_OPEN_SQL_DB occurs, if detected at runtime.
NON_V5_HINTS
ABAP_HINT_END
ABAP_VARIANT_10 ... CORR_SPEARMAN( sql_exp1,sql_exp2 )
What does it do?
Determines the Spearman's rank correlation coefficient of the values found in the corresponding rows of two columns. In other words, it measures the monotonous correlation of two value sets. The result of the SQL expressions sql_exp1 and sql_exp2 can have any numeric data type. The data type of the result is always FLTP. The result ranges from -1 to 1.
Latest notes:
If the aggregate function CORR_SPEARMAN is used as a window function, the ROWS BETWEEN addition is only supported with UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING. Otherwise, a syntax warning occurs, if detected at compile time, or a catchable exception of class CX_SY_OPEN_SQL_DB occurs, if detected at runtime.
NON_V5_HINTS
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Determination of the Pearson product-moment and the Spearman's rank correlation for columns NUM1 and NUM2 of table DEMO_EXPRESSIONS. CORR is 0.959, since the correlation is not linear. CORR_SPEARMAN is 1, since the value sets are correlated monotonously.
ABEXA 01434
ABAP_EXAMPLE_END
ABAP_VARIANT_11 ... STRING_AGG( sql_exp$[, sep$] $[ORDER BY ...$] )
What does it do?
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 than 1333, a catchable 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 consecutive 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 , or n with a maximum of 1333 characters. In sep, all trailing blanks are respected.
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 .
Latest notes:
The addition DISTINCT cannot be specified for the aggregate function STRING_AGG.
The aggregate function STRING_AGG cannot be used as a window function.
The restriction of the result length to 1333 can be removed by using the built-in function TO_CLOB. The use of TO_CLOB for STRING_AGG is recommended if the result length is expected to exceed 1333.
Respecting all trailing blanks in sep is different to the behavior of , where a truncation occurs. 