topic Re: Group By? in Application Development and Automation Discussions

Group By?

Former Member — Mon, 28 Aug 2006 09:28:11 GMT

Hi..

Where and why do we use Group By with a SELECT statement?

Is there any alternative to Group by in ABAP?

Thanks

Re: Group By?

Former Member — Mon, 28 Aug 2006 09:31:23 GMT

hi,

in selects if u want to use aggregate functions like max min avg .. then u have to use Group by on that filed.

ex: Select MAX( VERSION ) from tablexy into temp_version

where OBJECT = temp_object

group by version.

To know more about GROUP BY,Just check out this link

<b>http://www.sts.tu-harburg.de/teaching/sap_r3/ABAP4/groupby_.htm</b>

Regards

Ashok P

Re: Group By?

sushant_singh — Mon, 28 Aug 2006 09:47:11 GMT

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.

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.

Re: Group By?

Former Member — Mon, 28 Aug 2006 09:47:22 GMT

Refer this sample code:

DATA: count TYPE I, sum TYPE P DECIMALS 2, avg TYPE F.

DATA: connid TYPE sbook-connid.

SELECT connid COUNT( * ) SUM( luggweight ) AVG( luggweight )

INTO (connid, count, sum, avg)

FROM sbook

WHERE

carrid = 'LH' AND

fldate = '20010228'

GROUP BY connid.

WRITE: / connid, count, sum, avg.

ENDSELECT.

Regards,

ravi

Re: Group By?

Former Member — Mon, 28 Aug 2006 09:52:59 GMT

The Group by clause is used in the SELECT statement when you wish to aggregrate certain values in the table for a particular combination...u can use aggregrate functions like max, min, sum, count etc...

The alternative is to calculate the same using abap logic..basically selecting the data into an internal table and when you loop through the data to do the calculation using on change or at events.

Regards

Anurag

Re: Group By?

kanthimathikris — Mon, 28 Aug 2006 10:02:07 GMT

Let us take an example,

We have a table with three fields

EmpNo Month Salary

1 Jan 10000

2 Jan 8000

3 Jan 15000

1 Feb 10500

2 Feb 8200

3 Feb 15100

This table gives the salary for the employee in monthly basis.

Let us assume that you wanted to calculata the total salary of each employee

The output would be

EmpNO Salary

1 20500

2 16200

3 30100

In order to get the result you should use the aggregate function "sum" and the "group by" option in the select.

The other option is to download all values from the table in itab and sort the itab by empno.

Then loop itab,

loop at itab.

at new empno.

clear tot_sal.

endat.

tot_sal = tot_sal + sal.

at end of empno.

write empno tot_sal.

endat.

endloop.

Message was edited by: Kanthimathi Krishnan