We are hiring and constantly growing! Have a look through our vacancies to find the right role for you!
The addition GROUP BY combines groups of rows of the result set of a query into one row. After GROUP BY, the grouping criteria are specified statically or dynamically. The static specification is a comma-separated list of the following:
The order of the SQL expressions or grouping sets within the comma-separated list is not important. The dynamic specification is as a parenthesized data object grouping_syntax.
If used, GROUP BY demands that only individual elements col_spec and not all the columns are specified in the SELECT list using *. If GROUP BY is used, all columns that are specified directly after SELECT or as an argument of an SQL expression and not as the argument of an aggregate function, except the grouping function, must be specified in an aggregate expression. This means that columns not specified after GROUP BY can only be specified after SELECT as the argument of an aggregate function of an aggregate expression, with the exception of the grouping function. The aggregate expressions determine how the content of these columns is determined in the combined row from the content of all the rows of a group. For the grouping function GROUPING, the column that is specified as its argument must be listed after GROUP BY.
The SQL expressions specified after GROUP BY cannot have the type STRING, RAWSTRING, LCHR, LRAW, or GEOM_EWKB.
... sql_exp1, sql_exp2, ...
Specifies SQL expressions whose result is used for grouping. A group is formed by the rows that have the same result in all SQL expressions sql_exp1, sql_exp2, .... Every SQL expression specified after GROUP BY must also be specified somewhere in the SELECT list, with identical spelling. When specifying individual columns col, the same column names as in the SELECT list must be specified. It is not possible to specify alias names defined with AS.
When a column col is specified as the operand of an SQL expression after GROUP BY, the effect on the interaction with the SELECT list is the same as specifying the column individually. A column that is not the argument of an aggregate function in the SELECT list can be specified either individually after GROUP BY or as the operand of an SQL expression. If a column like this is specified as the operand of an SQL expression, it does not need to be specified individually.
The rows of the DDIC database table SFLIGHT that have the same content in column CARRID are combined. The lowest and highest values in column PRICE are determined for each of these groups and placed into the combined row.
Grouping after a concatenation of the columns CARRID and CONNID.
... grouping_sets1, grouping_sets2, ...
GROUPING SETS is an addition of the GROUP BY clause that can be used to define multiple
grouping sets grouping_sets1, grouping_sets2, ... under a GROUP BY clause. The
grouping sets are aggregated separately and grouped in a result set. For more information, see SELECT - GROUP BY, grouping_sets.
As an alternative to specifying columns statically, a parenthesized data object grouping_syntax can be specified, which either contains the syntax of the list of SQL expressions (with the exception of host expressions) or is initial when the statement is executed. The same applies to grouping_syntax as when specifying columns dynamically as a SELECT list.
If the content of grouping_syntax is initial, either all the rows or no rows are grouped together.
The columns in the SELECT list must
then be specified either solely as arguments of aggregate functions or only directly. If not, the catchable
exception CX_SY_OPEN_SQL_DB is raised. Invalid syntax raises a catchable exception from the class CX_SY_DYNAMIC_OSQL_ERROR.
See SQL Injections Using Dynamic Tokens.
After entering any column of DDIC database table SPFLI the selected data is organized according to this column, which means that similar entries are combined. In count the number of flight connections for the different values in column spflicol is determined. If, for example, CITYFROM is entered as spflicol, the number of destinations for each departure city is determined in count. Various possible exceptions are handled in TRY control structures. In particular, user input is checked for validity using a method of the class CL_ABAP_DYN_PRG.