Group by

[Former Member]

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

[Former Member]

Hi,

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

[Former Member]

Hi

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

[Former Member]

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.

[Former Member]

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.

[christine_evans]

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?