Selecting Groups of Lines
The HAVING clause uses conditions to restrict the number of groups selected.
You can only use the HAVING clause in conjunction with the GROUP-BYclause.
To select line groups, use:
SELECT lines s 1 [AS a 1 ] s 2 [AS a 2 ]... agg s m [AS a m ] agg s n [AS a n ]...... GROUP BY s 1 s 2 .... HAVING cond.
The conditions cond that you can use in the HAVING clause are the same as those in the SELECT clause , with the restrictions that you can only use columns from the SELECT clause, and not all of the columns from the database tables in the FROMclause. If you use an invalid column, a runtime error results.
On the other hand, you can enter aggregate expressions for all columns read from the database table that do not appear in the GROUP-BY clause. This means that you can use aggregate expressions, even if they do not appear in the SELECT clause. You cannot use aggregate expressions in the conditions in the WHERE clause.
As in the WHERE clause, you can specify the conditions in the HAVING clause as the contents of an internal table with line type c and length 72.
REPORT demo_select_group_by_having.
DATA wa TYPE sflight.
SELECT connid INTO wa-connid FROM sflight WHERE carrid = 'LH' GROUP BY connid HAVING SUM( seatsocc ) > 300.
WRITE: / wa-carrid, wa-connid.
ENDSELECT.
This example selects groups of lines from database table SFLIGHT with the value 'LH' for CARRID and identical values of CONNID. The groups are then restricted further by the condition that the sum of the contents of the column SEATSOCC for a group must be greater than 300.