ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Open SQL →  Open SQL - Read Accesses →  SELECT →  SELECT - result →  SELECT - select_list →  SELECT - col_spec →  SELECT - sql_exp →  Examples of SQL Expressions 

SQL Expressions, Use with GROUP BY

This example demonstrates SQL expressions combined with the addition GROUP BY.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA itab TYPE TABLE OF demo_expressions WITH EMPTY KEY.

    DELETE FROM demo_expressions.
    itab = VALUE #( ( id = 1 num1 = 1 num2 = 1 )
                    ( id = 2 num1 = 2 num2 = 4 )
                    ( id = 3 num1 = 3 num2 = 2 )
                    ( id = 4 num1 = 3 num2 = 2 )
                    ( id = 5 num1 = 5 num2 = 1 )
                    ( id = 6 num1 = 1 num2 = 1 )
                    ( id = 7 num1 = 1 num2 = 1 ) ).
    INSERT demo_expressions FROM TABLE @itab.

    data(out) = cl_demo_output=>new( ).

    out->begin_section( `GROUP BY num1, num2` ).
    SELECT num1 + num2 AS sum, COUNT( * ) AS count
           FROM demo_expressions
           GROUP BY num1, num2
           ORDER BY sum
           INTO TABLE @DATA(result1).
    out->write( result1 ).

    out->next_section( `GROUP BY num1 + num2` ).
    SELECT num1 + num2 AS sum, COUNT( * ) AS count
           FROM demo_expressions
           GROUP BY num1 + num2
           ORDER BY sum
           INTO TABLE @DATA(result2).
    out->write( result2 ).

    out->display( ).

Description

An arithmetic expression after SELECT adds the values of the columns NUM1 and NUM2 and passes the total to the results set SUM. The aggregate function count( * ) counts the rows of a group. NUM1 and NUM2 must be specified for the grouping after GROUP BY.

• The first SELECT statement specifies NUM1 and NUM2 as individual columns after GROUP BY. The rows are grouped by these columns. Distinct values in these columns produce separate groups, even if the total is the same.

• The second SELECT statement specifies NUM1 and NUM2 in an SQL expression after GROUP BY. The rows are grouped by the result of the expression.