Skip to Content
author's profile photo Former Member
Former Member

Re: group by

Hi,

when do we use group by and having clause in select stmt

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2007 at 04:46 AM

    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.

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2007 at 04:47 AM

    Hi

    Though they are SQL related commands we rarely use them in ABAP select statements

    we fetch the data into Internal table and then will use the

    SORT command to sort that itab by the required fields which serves the same purpose similar to GROUP By

    Regards

    Anji

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2007 at 04:47 AM

    HI,

    The addition GROUP BY combines groups of rows that have the same content in their specified columns (col1 col2 ...) in the resulting set into a single row.

    rgds,

    bharat.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 30, 2007 at 04:49 AM

    Hi,,

    When we want to get the records ordered by some fields we use the clause "group by " in select statement.

    But it s always better to sort the internal table after selection rather than using group by

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.