topic Re: Group by in Application Development and Automation Discussions

Group by

Former Member — Fri, 20 Jun 2008 10:51:14 GMT

I m using group by clause in my report.

select ebeln ebelp belnr max( bwart ) budat menge matnr werks

into table itab_ekbe1

from ekbe where werks eq p_werks

group by ebeln ebelp bwart matnr .

But is it showing an error, when i m not using all fields in group by which i selected, plz tell me what will b the corresct syntax.

The field "EKBE~WERKS" from the SELECT list is missing in the GROUP BY clause. addition INTO wa or INTO (g1,...,gn) is required. fields of type "" or "MATNR".

definiitely rewards pts

Re: Group by

Former Member — Fri, 20 Jun 2008 10:53:40 GMT

Hi,

When we use aggregate functions we need to include all the remaining fields in the group by clause.

Re: Group by

Former Member — Fri, 20 Jun 2008 10:59:12 GMT

try this:

Select <field1> <field2> <field3> from db_table

Group by <field2> <field3> <field1>

The Fields which you are selecting all need to be mentioned in the Group by clause.

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Jun 20, 2008 8:46 AM

Re: Group by

Former Member — Fri, 20 Jun 2008 11:03:24 GMT

hi check this example...

report .

tables:sbook.

select-options: s_carrid for sbook-carrid,

s_connid for 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,

sbook_type1 TYPE sbook_type .

SELECT fldate smoker COUNT( * ) AS smk_cnt

FROM sbook

INTO CORRESPONDING FIELDS OF TABLE sbook_tab

WHERE connid in s_connid

GROUP BY carrid fldate smoker

HAVING carrid in s_carrid

ORDER BY fldate smoker .

loop at sbook_tab into sbook_type1.

write:/ sbook_type1-fldate,

sbook_type1-smoker,

sbook_type1-smk_cnt .

endloop.

Re: Group by

Former Member — Fri, 20 Jun 2008 11:13:11 GMT

Hello Venkat,

when i m using max function for bwart with group by. While debugging it is not showing any entries for field bwart.

Plz tell me why it is not giving entries..

if eban_ekbe[] is not initial .

select ebeln ebelp max( bwart ) matnr werks

into corresponding fields of table itab_ekbe1

from ekbe where werks eq p_werks

and matnr = eban_ekbe-matnr

group by ebeln ebelp bwart matnr werks.

Re: Group by

christine_evans — Fri, 20 Jun 2008 12:43:46 GMT

Why are you including BWART in the GROUP BY when you are applying the aggregate function MAX to it? Have you read the post further up which clearly tells you that you shouldn't do this?