SQL Function for Select Statement

  SQL

Over View

Let’s touch on the functions DISTINCT, SUM, AVG, MAX, MIN, and COUNT that can be used in the SQL SELECT clause.
I think that there are many cases where the same thing is done with the internal table, so the usage frequency may be low, but if the number of data items is small and performance is not affected by either, consider using it. May be good.

DISTINCT

The duplicated result is returned.

DATA: IT_SFLIGHT type table of SFLIGHT.
SELECT DISTINCT CARRID
  FROM SFLIGHT INTO CORRESPONDING FIELDS OF TABLE IT_SFLIGHT.

SUM

Can be used for numeric items and returns the aggregate value in the unit used in the GROUP clause.
In the following example, the total amount of flight days will be returned.

DATA: IT_SFLIGHT type table of SFLIGHT.
SELECT FLDATE SUM( PRICE ) As PRICE
  FROM SFLIGHT INTO CORRESPONDING FIELDS OF TABLE IT_SFLIGHT
 GROUP BY FLDATE.

AVG

Can be used for numeric items and returns the average value in the unit used in the GROUP clause.
In the following example, the average price per flight day is returned.

DATA: IT_SFLIGHT type table of SFLIGHT.
SELECT FLDATE AVG( PRICE ) As PRICE
  FROM SFLIGHT INTO CORRESPONDING FIELDS OF TABLE IT_SFLIGHT
 GROUP BY FLDATE.

MAX

Can be used for numeric items and returns the maximum value in the unit used in the GROUP clause.
In the following example, the maximum amount of money within the flight date is returned.

DATA: IT_SFLIGHT type table of SFLIGHT.
SELECT FLDATE MAX( PRICE ) As PRICE
  FROM SFLIGHT INTO CORRESPONDING FIELDS OF TABLE IT_SFLIGHT
 GROUP BY FLDATE.

MIN

Can be used for numeric items and returns the minimum value within the unit used in the GROUP clause.
In the following example, the minimum amount within the flight date is returned.

DATA: IT_SFLIGHT type table of SFLIGHT.
SELECT FLDATE MIN( PRICE ) As PRICE
  FROM SFLIGHT INTO CORRESPONDING FIELDS OF TABLE IT_SFLIGHT
 GROUP BY FLDATE.

COUNT

Returns the number of records in the specified unit.
The following example returns the number of records for each flight day.
When used without using the GROUP clause, it returns the total number of records in the table.

TYPES: BEGIN OF T_CNT,
         FLDATE TYPE SFLIGHT-FLDATE,
         CNT    TYPE I,
       END OF T_CNT.
DATA: IT_CNT TYPE TABLE OF T_CNT.

SELECT FLDATE COUNT( * ) As CNT
  FROM SFLIGHT INTO CORRESPONDING FIELDS OF TABLE IT_CNT
 GROUP BY FLDATE.

See here for basic usage of SQL