Skip to content

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

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... agg_func ...

Effect

Aggregate expression in ABAP SQL. An aggregate expression consists of an aggregate function agg_func that aggregates the values of multiple rows of the results set of a query into a single value. It can be used in the following operand positions:

Here, an aggregate expression can be specified directly or as an operand of an SQL expression (except for another aggregate expression).
  • As an operand on the right side of relational expressions of the condition sql_cond of the HAVING clause of a query. Here, no SQL expressions can be used except for directly specified aggregate expressions.

Any aggregate functions can be used as an aggregate expression except for PRODUCT. An aggregate function used as an aggregate expression can have one of the following arguments:

  • An SQL expression sql_exp (except aggregate expressions and window expressions for the aggregate functions MAX, MIN, SUM, STRING_AGG, and COUNT).
  • A column col of a data source of a query for the aggregate functions AVG and GROUPING.

The aggregate functions take the values of the column or take the results of the SQL expression from all rows of the results set, or from the rows of the current group of a query constructed using GROUP BY, to determine a single value. If the value of an aggregate expression is too large for the target area, an exception is raised.


Notes

  • Table buffering is bypassed when aggregate expressions are used.
  • If SQL expressions other than directly specified columns are specified as arguments of aggregate expressions, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.


Example

Returns the flight date, the number of passengers, and the average and maximum luggage weight of all Lufthansa flights with the flight number 0400.

SELECT FROM sbook 
       FIELDS fldate, 
              COUNT( * ) AS count, 
              AVG( luggweight AS DEC( 16,4 ) ) AS avg, 
              MAX( luggweight ) AS max 
       WHERE carrid = 'LH' AND 
             connid = '0400' 
       GROUP BY fldate 
       INTO TABLE @DATA(result). 

cl_demo_output=>display( result ).

Executable Examples

Continue

sql_exp - agg_func

ABAP SQL - Examples of Aggregate Expressions