group by clause and having clause in select

[Former Member]

hi frnds

plz give me some information of group by and having clause used in select statement with example

thanks

[Former Member · Accepted solution]

The addition HAVING limits the nunber of lines to be grouped into groups in the resulting set by a logical expression sql_cond for these lines. The syntax of the logical expression sql_cond corresponds to the syntax of the logical expression sql_cond of the WHERE condition. The comparisons of the logical expression evaluate the contents of line groups.

If a grouping is done using the addition GROUP BY, all the columns that are specified in the condition sql_cond directly through their name col will be listed after GROUP BY. The direct specification of different columns leads to an exception CX_SY_OPEN_SQL_DB that can be handled. For any columns in the data base tables or Views listed after FROM, arbitrary aggregate expressions can be specified in the listed database tables in the comparisons of sql_cond. This kind of aggregate expression is evaluated for each line group defined in GROUP BY and its result is used as an operand in the comparison. If such a column is also listed simultaneously as an argument of an aggregate function after SELECT, the aggregate expressions after SELECT and after HAVING can be different.

If the addition GROUP BY is not specified or the data object column_syntax in the dynamic column specification after GROUP BY is initial, the addition HAVING can only be specified if the entire resulting set is grouped into a line - that is, if after SELECT you have solely aggregate expressions. In this case, solely aggregate expressions can be specified as operands in sql_cond. These operands are evaluated for all lines in the resulting set.

Note

The use of aggregate expressions as an operand in dyanmic HAVING conditions is possile only as of Release 6.10.

Example

Reading the number of booked smoking and non-smoking seats for each flight date of a particular flight connection.

PARAMETERS: p_carrid TYPE sbook-carrid,

p_connid TYPE sbook-connid.

TYPES: BEGIN OF sbook_type,

fldate TYPE sbook-fldate,

smoker TYPE sbook-smoker,

smk_cnt TYPE i,

END OF sbook_type.

DATA sbook_tab TYPE TABLE OF sbook_type.

SELECT fldate smoker COUNT( * ) AS smk_cnt

FROM sbook

INTO CORRESPONDING FIELDS OF TABLE sbook_tab

WHERE connid = p_connid

GROUP BY carrid fldate smoker

HAVING carrid = p_carrid

ORDER BY fldate smoker.

[Former Member]

Hai Rohit

Check the following Link.

It will provide Select Statement with Examples

http://www.sts.tu-harburg.de/teaching/sap_r3/ABAP4/select.htm

Thanks & regards

Sreenivasulu P

[Former Member]

Here is a example from help.sap.com

The HAVING clause is used to filter groups of a grouped table by a search condition.

Syntax

This graphic is explained in the accompanying text

<having clause> ::= HAVING <search condition>.

The <search condition> is applied to all groups of the grouped table. The result is a grouped table consisting of those groups, for which the <search condition> yields true.

If a HAVING clause is applied to a table that is not already grouped, the table is implicitly grouped. The result of implicitly grouping is grouped table with no grouping columns consisting of exactly one group. The <search condition> is applied to this group.

Example

This graphic is explained in the accompanying text

SELECT manager_name, AVG(salary) AS average

FROM employees AS e JOIN managers AS m

ON e.manager_id = m.manager_id

WHERE manager_name IN ('Paul Smith',

'Mary Jones',

'John Miles')

GROUP BY manager_name

HAVING AVG(salary) > ?

The HAVING Clause. For the three given managers, the average salary of all employees associated with the respective manager is created. Finally, only those managers are taken into account who have employees with an average salary that exceeds a given limit.

Regards,

Ravi

[vinod_gunaware2]

The Open SQL statement for reading data from database tables is:

SELECT <result>

INTO <target>

FROM <source>

[WHERE <condition>]

[GROUP BY <fields>]

[HAVING <cond>]

[ORDER BY <fields>].

The SELECT statement is divided into a series of simple clauses, each of which has a different part to play in selecting, placing, and arranging the data from the database.

You can only use the HAVING clause in conjunction with the GROUP BY clause.

To select line groups, use:

SELECT <lines> <s1> [AS <a1>] <s2> [AS <a2>] ...

<agg> <sm> [AS <am>] <agg> <sn> [AS <an>] ...

...

GROUP BY <s1> <s2> ....

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 FROM clause. 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.

Example

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.

The <b>GROUP BY</b> clause summarizes several lines from the database table into a single line of the selection.

The GROUP BY clause allows you to summarize lines that have the same content in particular columns. Aggregate functions are applied to the other columns. You can specify the columns in the GROUP BY clause either statically or dynamically.

Specifying Columns Statically

To specify the columns in the GROUP BY clause statically, use:

SELECT <lines> <s1> [AS <a 1>] <s 2> [AS <a 2>] ...

<agg> <sm> [AS <a m>] <agg> <s n> [AS <a n>] ...

...

GROUP BY <s1> <s 2> ....

To use the GROUP BY clause, you must specify all of the relevant columns in the SELECT clause. In the GROUP BY clause, you list the field names of the columns whose contents must be the same. You can only use the field names as they appear in the database table. Alias names from the SELECT clause are not allowed.

All columns of the SELECT clause that are not listed in the GROUP BY clause must be included in aggregate functions. This defines how the contents of these columns is calculated when the lines are summarized.

Specifying Columns Dynamically

To specify the columns in the GROUP BY clause dynamically, use:

... GROUP BY (<itab>) ...

where <itab> is an internal table with line type C and maximum length 72 characters containing the column names <s 1 > <s 2 > .....

Example

DATA: CARRID TYPE SFLIGHT-CARRID,

MINIMUM TYPE P DECIMALS 2,

MAXIMUM TYPE P DECIMALS 2.

SELECT CARRID MIN( PRICE ) MAX( PRICE )

INTO (CARRID, MINIMUM, MAXIMUM)

FROM SFLIGHT

GROUP BY CARRID.

WRITE: / CARRID, MINIMUM, MAXIMUM.

ENDSELECT.

regards

vinod