Ansicht
Dokumentation

ABENSQL_AGG_FUNC - SQL AGG FUNC

ABENSQL_AGG_FUNC - SQL AGG FUNC

PERFORM Short Reference   ABAP Short Reference  
This documentation is copyright by SAP AG.
SAP E-Book

- agg_func

... AVG( $[DISTINCT$] col $[AS dtype$] )
  $| MEDIAN( $[DISTINCT$] sql_exp )
  $| MAX( $[DISTINCT$] sql_exp )
  $| MIN( $[DISTINCT$] sql_exp )
  $| SUM( $[DISTINCT$] sql_exp )
  $| PRODUCT( col )
  $| STDDEV( $[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( ... )


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. ... STDDEV( $[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( ... )

Effect

Aggregate function in . 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 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
AVG x x - -
MEDIAN x x - -
MAX x x x x
MIN x x x x
SUM x x x x
PRODUCT - - - x
STDDEV x x - -
VAR x x - -
CORR x x - -
CORR_SPEARMAN x x - -
STRING_AGG x - - x
COUNT x x x x
COUNT(*) x x x x
GROUPING x - - -
ALLOW_PRECISION_LOSS 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 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.

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 an intermediate result, is of type FLTP. The addition AS dtype can be used to force the type FLTP.

Example

Determination of the average value of all values in a column and checking of the result.

Variant 2

... MEDIAN( $[DISTINCT$] sql_exp )


Effect

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.

Example

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.

Variant 3

... MAX( $[DISTINCT$] sql_exp )


Variant 4

... 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

Determination of the minimum value and maximum value of all values in a column and checking of the result.

Variant 5

... 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

Summation of all values in a column and checking the result.

Variant 6

... 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.

See Hierarchy Navigator HIERARCHY_ANCESTORS_AGGREGATE

Variant 7

... STDDEV( $[DISTINCT$] sql_exp )


Effect

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 STDDEV 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.

Variant 8

... VAR( $[DISTINCT$] sql_exp )


Effect

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.

Example

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.

Variant 9

... CORR( sql_exp1,sql_exp2 )


Effect

Determines the Pearson product momentum 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.

Variant 10

... CORR_SPEARMAN( sql_exp1,sql_exp2 )


Effect

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.

Example

Determination of the Pearson product momentum 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.

Variant 11

... 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 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.

Notes

  • The addition DISTINCT cannot be specified for the aggregate function STRING_AGG.
  • When used, the aggregate function STRING_AGG requires the strict mode from Release .
  • Respecting all trailing blanks in sep is different to the behavior of &&, where a truncation occurs.

SQL Expressions, Aggregate Function string_agg

Variant 12

... 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.

Example

Determination of the number of airlines flying to New York.

Variant 13

... COUNT( * )


Variant 14

... 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 for results outside of the value range of the type i.

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 result set determined using COUNT(*) and is independent of 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 and 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.

Variant 15

... 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. For more information, see sql_agg - GROUPING.

GROUPING, Aggregate Function

Variant 16

ALLOW_PRECISION_LOSS( ... )


Effect

The ALLOW_PRECISION_LOSS statement improves the performance of an aggregate expression agg_exp at the cost of accuracy of the result. This function should only be used on decimal values and when loss of precision is acceptable. Currently, SUM is the only supported aggregate expression.

For more information, see sql_agg - ALLOW_PRECISION_LOSS.

sql_agg - Aggregate Function ALLOW_PRECISION_LOSS






Vendor Master (General Section)   rdisp/max_wprun_time - Maximum work process run time  
This documentation is copyright by SAP AG.

Length: 31995 Date: 20240329 Time: 145522     sap01-206 ( 408 ms )