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

# sql_agg - AVG, AS

Other versions:
| *7.31* | *7.40***7.54**

## Syntax

```
... AVG(
```*[*DISTINCT*]* col AS dtype ) ...

### Effect

Aggregate function `AVG`

in
ABAP SQL with a data type `dtype`

specified. The data type determines the result
type in which the average value is returned. The data type of `col`

must be numeric and have one of the types
INT1,
INT2,
INT4,
INT8,
DEC,
CURR,
QUAN, or
FLTP. The addition `AS`

cannot yet be used for all types for
decimal floating point numbers.

The data type `dtype`

can have the built-in numeric type
DEC,
CURR,
QUAN,
DECFLOAT16,
DECFLOAT34, or
FLTP. The data type FLTP, however, can only be specified for operands of type FLTP. The data type `dtype`

can be specified as follows:

`DEC( len,decimals )`

`CURR( len,decimals )`

`QUAN( len,decimals )`

`D16N`

for DECFLOAT16

`D34N`

for DECFLOAT34

`FLTP`

Length and decimal places must be specified with `len`

and `decimals`

.
Literals or
host constants of the
ABAP type `b`

,
s, `i`

, or `int8`

can be specified for
len and `decimals`

. The value range of the data type `dtype`

must cover the value range of the operand type:

- If
`col`

is an integer type, a type`dtype`

of type DEC, CURR, QUAN must have at least as many integer digits as is required by the value range of the respective integer type, that is, 3 for INT1, 5 for INT2, 10 for INT4, and 19 for INT8. There is no restriction on the number of decimal places.

- If
`col`

has type DEC, CURR, QUAN, a type`dtype`

of type DEC, CURR, QUAN must have at least any many integer digits and decimal places.

- If
`col`

has type FLTP, this type must also be specified for`dtype`

.

The data types DECFLOAT16 and DECFLOAT34 can be used for all permitted data types of `col`

except FLTP. If the average value cannot be represented exactly in the data type `dtype`

, the result is rounded commercially.

Notes

- The addition
`AS`

to`AVG`

bridges the gap for average values where the default result type FLTP cannot be cast to other numeric data types with`CAST`

.

- Since average values usually have decimal places, integer types are not supported for
`dtype`

. If necessary, the type DEC can be used without decimal places.

- An aggregate expression
`AVG`

cannot be applied to operands with the data type INT8 without using the addition`AS`

. The addition`AS`

can also be used to form average values for columns with data type INT8. However, for the types DF16_..., DF34_...,`AVG`

is only possible without the addition`AS`

.

- The actual calculation of the average value (determined by the involved data types) is platform
dependent. This can lead to underflows, overflows and exceptions during the calculation. To prevent
exceptions, the total of the values for which the average value is being calculated should be within the value range of
`dtype`

.

- If FLTP is specified explicitly for operands of type FLTP, this ensures that the result of
`AVG`

actually has this type at each operand position, which cannot be guaranteed otherwise.

- If used,
`D16N`

or`D34N`

force the strict mode of the syntax check from Release 7.54.

Example

Apply the aggregate function `AVG`

to a column of the database table DEMO_EXPRESSIONS.
The program DEMO_SQL_AVG executes this access to the table and represents the result.

```
SELECT
FROM demo_expressions
FIELDS
AVG( num1 ) AS avg_no_type,
AVG( DISTINCT num1 ) AS avg_no_type_distinct,
AVG( num1 AS DEC( 10,0 ) ) AS avg_dec0,
AVG( DISTINCT num1 AS DEC( 10,0 ) ) AS avg_dec0_distinct,
AVG( num1 AS DEC( 14,4 ) ) AS avg_dec4,
AVG( DISTINCT num1 AS DEC( 14,4 ) ) AS avg_dec4_distinct
INTO @DATA(result).
```