SAP Learning Journey

Sorting and Condensing Data Sets in ABAP SQL

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Request sorted result sets from the database
  • Retrieve condensed and aggregated data sets

Sorted Result Sets

In this video, you will see how to sort the result set of a SELECT statement.

Try It Out: ORDER BY

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
    
    SELECT FROM /dmo/flight
         FIELDS carrier_id,
                connection_id,
                flight_date,
                seats_max - seats_occupied AS seats
          WHERE carrier_id     = 'AA'
            AND plane_type_id  = 'A320-200'
*       ORDER BY seats_max - seats_occupied DESCENDING,
*                flight_date                ASCENDING
           INTO TABLE @DATA(result).

    out->write(
      EXPORTING
        data   = result
        name   = 'RESULT'
    ).
    Expand
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.
  5. Remove the star signs (*) in the lines with the ORDER BY addition and activate again.
  6. Execute again and see how the sort order of the console output changed.
  7. Play around with the ORDER BY addition. Change the sorting direction, sort the result by other fields or expressions to get familiar with the syntax.

Unique Result Sets

Addition DISTINCT allows you to remove duplicates from the result set of a SELECT statement. The addition has to be placed right before the first element of the field list.

Watch this video to know how.

Try It Out: SELECT DISTINCT

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
    
 SELECT FROM /dmo/connection
    FIELDS
*           DISTINCT
           airport_from_id,
           distance_unit

  ORDER BY airport_from_id
      INTO TABLE @DATA(result).

    out->write(
      EXPORTING
        data   = result
        name   = 'RESULT'
    ).
    Expand
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.
  5. Remove the star signs (*) in the line with the DISTINCT addition and activate again.
  6. Execute again and see how the console output changed.

Aggregate Functions

An aggregate expression calculates a single value from an sql_expression (sql_exp) by calling an aggregate function for multiple rows of a results set.

Only Aggregate Functions in Field List

If the field list of a SELECT statement contains nothing but aggregate functions, the result set always consists of exactly one row. Note that this is also the case if the aggregation is based on an empty data set.

In our example, the SELECT statement on the left illustrates the data set on which the aggregations in the SELECT on right are based.

You can see that the maximum in column DISTANCE is 6217 while the minimum is 6162. The total number of rows is 4 and column AIRPORT_FROM_ID contains 3 distinct values (value 'FRA' appears twice).

Try It Out: Aggregate Functions

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
    
    SELECT FROM /dmo/connection
         FIELDS carrier_id,
                connection_id,
                airport_from_id,
                distance
          WHERE carrier_id = 'LH'
           INTO TABLE @DATA(result_raw).


    out->write(
      EXPORTING
        data   = result_raw
        name   = 'RESULT_RAW'
    ).

*********************************************************************

    SELECT FROM /dmo/connection
         FIELDS MAX( distance ) AS max,
                MIN( distance ) AS min,
                SUM( distance ) AS sum,
                AVG( distance ) AS average,
                COUNT( * ) AS count,
                COUNT( DISTINCT airport_from_id ) AS count_dist

          WHERE carrier_id = 'LH'
           INTO TABLE @DATA(result_aggregate).

    out->write(
      EXPORTING
        data   = result_aggregate
        name   = 'RESULT_AGGREGATED'
    ).
    Expand
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.

Groups and Aggregates

If the field list of a SELECT statement contains a mixture of aggregate functions and other elements, for example, columns, expressions, and so on, the syntax of SQL requires a GROUP BY clause. The GROUP BY clause has to list all columns that appear in the field list outside of aggregations.

In the SELECT statement on the right, two columns are used in the field list: CARRIER_ID and DISTANCE. Column DISTANCE only appears as input for aggregate functions and therefore is not required after GROUP BY. But column CARRIER_ID is used outside of the aggregate functions and therefore has to be listed after GROUP BY.

The effect of GROUP BY can be see in the result of the SELECT statement. The data set, on which the aggregation is performed, is separated into subsets (groups) that have the same values in the GROUP BY - fields. The final result set contains one row for each group. The aggregate expressions are evaluated for each group separately.

For example, the row with CARRIER_ID = 'AA' contains COUNT = 5 and MAX = 4156 because the subset with CARRIER_ID = 'AA' contains 5 rows and within this subset the maximum value for distance is 4156.

Try It Out: GROUP BY

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
    
SELECT FROM /dmo/connection
     FIELDS
*            carrier_id,

            MAX( distance ) AS max,
            MIN( distance ) AS min,
            SUM( distance ) AS sum,
            COUNT( * ) AS count

*     GROUP BY carrier_id
     INTO TABLE @DATA(result).
    out->write(
      EXPORTING
        data   = result
        name   = 'RESULT'
    ).
    Expand
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.
  5. Remove the star sign (*) in the line after the line with key word FIELDS (the one with field name carrier_id). Notice the syntax error that tells you that field CARRIER_ID is missing in the GROUP BY clause.
  6. Remove the star sign (*) from the line with the GROUP BY addition and activate again.
  7. Execute again and see how the console output changed.

Log in to track your progress & complete quizzes