SAP® Documentation

Single view

ABAPAGGREGATE_FUNCTIONS - AGGREGATE FUNCTIONS

SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up   Vendor Master (General Section)  
This documentation is copyright by SAP AG.
SAP E-Book

Aggregate Functions

Variants:

1. ... MAX( [DISTINCT] fdescriptor )

2. ... MIN( [DISTINCT] fdescriptor )

3. ... AVG( [DISTINCT] fdescriptor )

4. ... SUM( [DISTINCT] fdescriptor )

5. ... COUNT( DISTINCT fdescriptor )

6. ... COUNT( * )

Effect

You can use aggregate functions in the SELECT and HAVING clauses in the SELECT and OPEN CURSOR statements, to group together data from one or more columns in a database table in the resulting set.



Note

The database column whose values are to be aggregated must not have the type STRING or RAWSTRING.

Variant 1

... MAX( [DISTINCT] fdescriptor ).


Effect

For the lines selected, returns the largest value in the column specified by the field labelfdescriptor. The DISTINCT declaration does not affect the result. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is the NULL value.

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the highest ticket price for each flight, ordered by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      max      TYPE sbook-loccuram.

SELECT scustom~name scustom~postcode scustom~city
         MAX( sbook~loccuram )
       INTO (name, postcode, city, max)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY scustom~name.
  WRITE: / name, postcode, city, max.
ENDSELECT.

Variant 2

... MIN( [DISTINCT] fdescriptor ).


Effect

For the lines selected, returns the smallest value in the column specified by the field labelfdescriptor. The DISTINCT declaration does not affect the result. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is the NULL value.

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the lowest ticket price for each flight, ordered by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      min      TYPE sbook-loccuram.

SELECT scustom~name scustom~postcode scustom~city
         MIN( sbook~loccuram )
       INTO (name, postcode, city, min)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY scustom~name.
  WRITE: / name, postcode, city, min.
ENDSELECT.

Variant 3

... AVG( [DISTINCT] fdescriptor ).


Effect

For the lines selected, returns the mean of all the values in the column specified by the field labelfdescriptor. You can only use AVG on a numeric-type field. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is the NULL value. Thus, when the mean is calculated, only those values not equal to NULL will be included.

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the mean ticket price for each flight, ordered by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      average  TYPE sbook-loccuram.

SELECT scustom~name scustom~postcode scustom~city
         AVG( sbook~loccuram ) AS avg
       INTO (name, postcode, city, average)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY avg DESCENDING scustom~name.
  WRITE: / name, postcode, city, average.
ENDSELECT.

Variant 4

... SUM( [DISTINCT] fdescriptor ).


Effect

Effect
For the lines selected, returns the sum of all the values in the column specified by the field labelfdescriptor. You can only use SUM with a numeric-type field. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is the NULL value.

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the sum of ticket prices for each flight, ordered by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      sum      TYPE sbook-loccuram.

SELECT scustom~name scustom~postcode scustom~city
         SUM( sbook~loccuram )
       INTO (name, postcode, city, sum)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY scustom~name.
  WRITE: / name, postcode, city, sum.
ENDSELECT.

Variant 5

... COUNT( DISTINCT fdescriptor ).


Effect

For the lines selected, returns the number of different values in the column specified by the field labelfdescriptor. The DISTINCT declaration is compulsory. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is 0.

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the number of different ticket prices for each flight, ordered by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      count    TYPE I.

SELECT scustom~name scustom~postcode scustom~city
         COUNT( DISTINCT sbook~loccuram )
       INTO (name, postcode, city, count)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY scustom~name.
  WRITE: / name, postcode, city, count.
ENDSELECT.

Variant 6

... COUNT( * ).


Effect

Returns the number of lines selected. If the SELECT command contains a GROUP-BY clause, the system returns the number of lines for each group. You can use COUNT( * ) instead of COUNT(*).

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the number of bookings for each flight, ordered by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      count    TYPE I.

SELECT scustom~name scustom~postcode scustom~city COUNT( * )
       INTO (name, postcode, city, count)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY scustom~name.
  WRITE: / name, postcode, city, count.
ENDSELECT.

Exceptions

Non-Catchable Exceptions

  • Cause: The database column whose values are to be aggregated has the type STRING or RAWSTRING.
    Runtime Error:SAPSQL_AGGREGATE_LOB
  • Cause: The database column for which the mean is to be calculated does not have a numeric type.
    Runtime Error:SAPSQL_FIELDLIST_AVG_TYPE
  • Cause: DThe database column whose values are to be totalled does not have a numeric type.
    Runtime Error:SAPSQL_FIELDLIST_SUM_TYPE


Additional help

Reading Data






RFUMSV00 - Advance Return for Tax on Sales/Purchases   SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up  
This documentation is copyright by SAP AG.

Length: 16386 Date: 20191208 Time: 223227     sap01-206 ( 40 ms )

Our Service

Looking for Support? Questions?

The

Consolut

Callback-Service

Leave us your contact details and we will call you back. Panels marked with * are mandatory.