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