Ansicht
Dokumentation
ABAPAGGREGATE_FUNCTIONS - AGGREGATE FUNCTIONS
CL_GUI_FRONTEND_SERVICES - Frontend Services Fill RESBD Structure from EBP Component StructureThis documentation is copyright by SAP AG.
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
SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up PERFORM Short Reference
This documentation is copyright by SAP AG.
Length: 16044 Date: 20240416 Time: 205030 sap01-206 ( 177 ms )
