Aggregate functions return a single value from the values in several lines of a column in a database table.The value is calculated in the database system.
These functions can be used with SELECT statement by specifying a column(column identifier) of a database table as an argument.
Any number of column identifiers can be used as arguments of aggregate functions in SELECT statement.
Also multiple aggregate functions can be used in a single SELECT statement.
Following are the aggregate functions available :
Average(AVG) , MAXIMUM(MAX) , Minimum(MIN) , Total(SUM) , Count(COUNT) .
Average(AVG) :
Syntax : AVG( [DISTINCT] col ) AVG determines the average value of the column col specified. It can only be applied to a numeric field.
Data types like DF16_DEC, D34_DEC (decimal floating point numbers ) , F(float) are recommended for the target fields.
Example :
DATA: lv_avgnetwr TYPE f.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT AVG( netwr ) FROM vbap INTO lv_avgnetwr WHERE vbeln EQ p_vbeln.
Maximum(MAX) :
Syntax : MAX( [DISTINCT] col )
MAX determines the maximum value of the contents of the column col specified.
Example :
DATA: lv_maxkwmeng TYPE kwmeng.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT MAX( kwmeng ) FROM vbap INTO lv_maxkwmeng WHERE vbeln EQ p_vbeln.
Minimum(MIN) :
Syntax : MIN( [DISTINCT] col )
MIN determines the minimum value of the contents of the column col specified.
Example :
DATA: lv_minkwmeng TYPE kwmeng.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT MIN( kwmeng ) FROM vbap INTO lv_minkwmeng WHERE vbeln EQ p_vbeln.
Total(SUM) :
Syntax : SUM( [DISTINCT] col )
SUM determines the sum of contents of the column col specified.This can only be applied to a numeric field.
To avoid overflows it is recommended to make data type of target field greater than that of the source field.
Example :
DATA: lv_sumkwmeng TYPE p decimals 2.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT SUM( kwmeng ) FROM vbap INTO lv_sumkwmeng WHERE vbeln EQ p_vbeln.
Count(COUNT) :
1. Syntax : COUNT( DISTINCT col )
It determines the number of different values in the column col .
Example :
DATA: lv_countmatnr TYPE int4.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT COUNT( DISTINCT matnr ) FROM vbap INTO lv_countmatnr WHERE vbeln EQ p_vbeln.
2. Syntax : COUNT( * )
It determines the number of rows in the result set. No column identifier col is specified in this case.
DATA: lv_counttotal TYPE i.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT COUNT( * ) FROM vbap INTO lv_counttotal WHERE vbeln EQ p_vbeln.
Some interesting points about aggregate functions :
1. When aggregate functions are used,the SELECT statement bypasses SAP table buffering. It is a disadvantage if tables are buffered.
2. Columns of the type STRING or RAWSTRING cannot be used with aggregate functions.
3. Pooled and Cluster tables do not any support aggregate functions other than COUNT (*).
4. If the addition FOR ALL ENTRIES is used before WHERE , apart from COUNT(*) no other aggregate function can be used.
5. By adding a GROUP BY clause at the end of our select statement, we can group together fields to give a total value for each grouped field.
6. NULL values are not included in the calculation of aggregate functions.The result is a null value only if all the rows in the column in the question contain a null value.
These functions can be used with SELECT statement by specifying a column(column identifier) of a database table as an argument.
Any number of column identifiers can be used as arguments of aggregate functions in SELECT statement.
Also multiple aggregate functions can be used in a single SELECT statement.
Following are the aggregate functions available :
Average(AVG) , MAXIMUM(MAX) , Minimum(MIN) , Total(SUM) , Count(COUNT) .
Average(AVG) :
Syntax : AVG( [DISTINCT] col ) AVG determines the average value of the column col specified. It can only be applied to a numeric field.
Data types like DF16_DEC, D34_DEC (decimal floating point numbers ) , F(float) are recommended for the target fields.
Example :
DATA: lv_avgnetwr TYPE f.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT AVG( netwr ) FROM vbap INTO lv_avgnetwr WHERE vbeln EQ p_vbeln.
Maximum(MAX) :
Syntax : MAX( [DISTINCT] col )
MAX determines the maximum value of the contents of the column col specified.
Example :
DATA: lv_maxkwmeng TYPE kwmeng.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT MAX( kwmeng ) FROM vbap INTO lv_maxkwmeng WHERE vbeln EQ p_vbeln.
Minimum(MIN) :
Syntax : MIN( [DISTINCT] col )
MIN determines the minimum value of the contents of the column col specified.
Example :
DATA: lv_minkwmeng TYPE kwmeng.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT MIN( kwmeng ) FROM vbap INTO lv_minkwmeng WHERE vbeln EQ p_vbeln.
Total(SUM) :
Syntax : SUM( [DISTINCT] col )
SUM determines the sum of contents of the column col specified.This can only be applied to a numeric field.
To avoid overflows it is recommended to make data type of target field greater than that of the source field.
Example :
DATA: lv_sumkwmeng TYPE p decimals 2.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT SUM( kwmeng ) FROM vbap INTO lv_sumkwmeng WHERE vbeln EQ p_vbeln.
Count(COUNT) :
1. Syntax : COUNT( DISTINCT col )
It determines the number of different values in the column col .
Example :
DATA: lv_countmatnr TYPE int4.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT COUNT( DISTINCT matnr ) FROM vbap INTO lv_countmatnr WHERE vbeln EQ p_vbeln.
2. Syntax : COUNT( * )
It determines the number of rows in the result set. No column identifier col is specified in this case.
DATA: lv_counttotal TYPE i.
PARAMETERS: p_vbeln TYPE vbeln_va.
SELECT COUNT( * ) FROM vbap INTO lv_counttotal WHERE vbeln EQ p_vbeln.
Some interesting points about aggregate functions :
1. When aggregate functions are used,the SELECT statement bypasses SAP table buffering. It is a disadvantage if tables are buffered.
2. Columns of the type STRING or RAWSTRING cannot be used with aggregate functions.
3. Pooled and Cluster tables do not any support aggregate functions other than COUNT (*).
4. If the addition FOR ALL ENTRIES is used before WHERE , apart from COUNT(*) no other aggregate function can be used.
5. By adding a GROUP BY clause at the end of our select statement, we can group together fields to give a total value for each grouped field.
6. NULL values are not included in the calculation of aggregate functions.The result is a null value only if all the rows in the column in the question contain a null value.