ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  SELECT clauses →  SELECT - GROUP BY 

SELECT, GROUP BY for SQL Expressions

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

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DELETE FROM demo_expressions.
    INSERT demo_expressions FROM TABLE @( 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 ) ) ).

    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.