Sorting and Condensing Data Sets in ABAP SQL

Objectives

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
    123456789101112131415161718
    
    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'
    ).
  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
    12345678910111213141516
    
 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'
    ).
  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.

Some important aggregate functions are:

  • SUM( sql_exp )

    Determines the sum of the results of the SQL expression sql_exp in a row set. The data type of the result set is the type of the SQL expression. The result type of the SQL expression sql_exp must be a numeric type.

  • MIN( sql_exp ) and MAX( sql_exp )

    Determines the maximum value or minimum value of the results of the SQL expression sql_exp in a row set. The data type of the result is the type of the result of the SQL expression.

    The result of the SQL expression sql_exp must be a numeric type. If a single column is specified for the expression, this column can also have any data type.

  • AVG( col [ AS dtype ] )

    Determines the average value of the content of a column col in a row set. By default, the result type is FLTP. Optional addition AS dtype can be used to define the result type explicitly. The data type of the column must be numeric.

    Note

    SQL expressions cannot be specified as arguments for AVG.

  • COUNT( * ) or COUNT(*)

    The two spellings have the same meaning and produce the number of rows in a row set. In most cases, the result type is INT4. The result type is INT8 if COUNT( * ) or COUNT(*) is the only element in the field list.

  • COUNT( DISTINCT sql_exp )

    Determines the number of distinct values of the results of the SQL expression sql_exp in a row set. The data type of the result is INT4.

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
    1234567891011121314151617181920212223242526272829303132333435
    
    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'
    ).
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.

The GROUP BY addition

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
    123456789101112131415161718
    
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'
    ).
  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.

Retrieve Sorted and Aggregated Data

In this exercise, you sort the flights by date and calculate the average occupation of flights on the database.

Template:

  • /LRN/CL_S4D401_DBS_FUNCTIONS (Global Class)

Solution:

  • /LRN/CL_S4D401_DBS_AGGREGATE (Global Class)

Task 1: Copy Template (Optional)

Copy the template class /LRN/CL_S4D401_DBS_FUNCTIONS. If you finished the previous exercise, you can skip this task and continue editing your class ZCL_##_SOLUTION.

Steps

  1. Copy class /LRN/CL_S4D401_DBS_FUNCTIONS to a class in your own package (suggested name: ZCL_##_SOLUTION, where ## stands for your group number).

    1. In the Project Explorer, right-click class /LRN/CL_S4D401_DBS_FUNCTIONS to open the context menu.

    2. From the context menu, choose Duplicate....

    3. Enter the name of your package in the Package field. In the Name field, enter ZCL_##_SOLUTION, where ## stands for your group number.

    4. Choose Next.

    5. Confirm the transport request and choose Finish.

  2. Activate the copy.

    1. Press Ctrl + F3 to activate the class.

Task 2: Sort Data

In local class LCL_PASSENGER_FLIGHT, adjust the implementation of method GET_FLIGHTS_BY_CARRIER. Add a suitable directive to retrieve the list of flights ordered by the flight date in ascending order. Then do the same in local class LCL_CARGO_FLIGHT.

Steps

  1. In local class LCL_PASSENGER_FLIGHT, navigate to the implementation of method GET_FLIGHTS_BY_CARRIER.

    1. Perform this step as before.

  2. Adjust the SELECT statement to make sure the database returns the result sorted by the flight date in ascending order.

    1. Add an ORDER BY clause before the INTO clause.

    2. Adjust the code as follows:

      ABAP
      1234567891011121314151617
        SELECT
    FROM /lrn/passflight
    FIELDS carrier_id, connection_id, flight_date,
           plane_type_id, seats_max, seats_occupied,
           seats_max - seats_occupied AS seats_free,
           currency_conversion(
             amount             = price,
             source_currency    = currency_code,
             target_currency    = @currency,
             exchange_rate_date = flight_date,
             on_error           =
               @sql_currency_conversion=>c_on_error-set_to_null
                              ) AS price,
           @currency AS currency_code    
    WHERE carrier_id = @i_carrier_id
    ORDER BY flight_date ASCENDING
    INTO TABLE @flights_buffer.

  3. Navigate to method GET_FLIGHTS_BY_CARRIER in local class LCL_CARGO_FLIGHT.

    1. Perform this step as before.

  4. Adjust the SELECT statement to make sure the database returns the result sorted by the flight date in ascending order.

    1. Add an ORDER BY clause before the INTO clause.

    2. Adjust the code as follows:

      ABAP
      12345678
        SELECT
    FROM /lrn/cargoflight
    FIELDS carrier_id, connection_id, flight_date,
           plane_type_id, maximum_load, actual_load, load_unit,
           airport_from_id, airport_to_id, departure_time, arrival_time
    WHERE carrier_id = @i_carrier_id
    ORDER BY flight_date ASCENDING
    INTO CORRESPONDING FIELDS OF TABLE @flights_buffer.

Task 3: Use Aggregate Function

In local class LCL_CARRIER, adjust the implementation of method GET_AVERAGE_FREE_SEATS. Instead of calculating the result in a loop over all passenger flights, do the calculation with a SELECT from database table /LRN/PASSFLIGHT.

Steps

  1. In local class LCL_CARRIER, navigate to the implementation of method GET_AVERAGE_FREE_SEATS.

    1. Perform this step as before.

  2. Delete or comment the source code of the method implementation.

    1. Mark the code between statements METHOD get_average_free_seats. and ENDMETHOD. and press Ctrl + <.

  3. Now implement a SELECT statement that retrieves the sum of free seats and the number of passenger flights for the current airline, which is stored in the carrier_id attribute. Store the result in an inline-declared data object (suggested name: aggregates).

    1. Add the following code:

      ABAP
      12345
        SELECT FROM /lrn/passflight
    FIELDS SUM( seats_max - seats_occupied ) AS sum, 
           COUNT(*) AS count
    WHERE carrier_id = @carrier_id
    INTO @DATA(aggregates).

  4. After the SELECT statement, calculate the ratio of the two aggregates and assign the result to returning parameter r_result.

    1. Adjust the code as follows:

      ABAP
      1234567
        SELECT FROM /lrn/passflight
    FIELDS SUM( seats_max - seats_occupied ) AS sum,
           COUNT(*) AS count
    WHERE carrier_id = @carrier_id
    INTO @DATA(aggregates).

  r_result = aggregates-sum / aggregates-count.

      Note

      An even more concise implementation of the GET_AVERAGE_FREE_SEATS method could be achieved through the following code:
      ABAP
      1234
        SELECT FROM /lrn/passflight
    FIELDS CAST( AVG( seats_max - seats_occupied ) AS INT4 )
    WHERE carrier_id = @carrier_id
    INTO @r_result.

  5. Activate and test your global class as console app.

    1. Press Ctrl + F3.

    2. Press F9.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn