topic Re: group by syntax does not work in inner join statement in Application Development and Automation Discussions

group by syntax does not work in inner join statement

siongchao_ng — Thu, 20 Jun 2013 00:39:42 GMT

Hi all,

I need to make a select statement and group by 3 given fields namely batch material and plant. Following is my statement and I am getting the error:

Error message:

The field "C~MAKTX" from the SELECT list is is missing in the GROUP BY

clause. missing in the GROUP BY clause. missing in the GROUP BY clause.

is missing in the GROUP BY clause. is missing in the GROUP BY clause.

is "C~MATNR".

Anyone knows whether group by can be used in inner join or have I put in wrongly?

   SELECT a~charg
         a~matnr
         a~werks
         a~menge
         a~kdauf
         a~shkzg
         b~matkl
         c~maktx
INTO CORRESPONDING FIELDS OF TABLE lt_product
FROM aufm AS a
INNER JOIN mara AS b
ON a~mandt = b~mandt
AND a~matnr = b~matnr
INNER JOIN makt AS c
ON b~mandt = c~mandt
AND b~matnr = c~matnr
WHERE a~charg IN s_batch
AND   a~werks EQ p_plant
AND ( a~bwart = '101' OR a~bwart = '102' )
GROUP BY a~charg a~matnr a~werks.

Re: group by syntax does not work in inner join statement

raviahuja — Thu, 20 Jun 2013 02:25:59 GMT

Hi Siong,

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.

Also, If GROUP BY is used, all columns that are specified directly after SELECT and not specified as an argument of an aggregate function must be listed there. Conversely, if GROUP BY is used, all the columns listed after SELECT that are not specified after GROUP BY must be specified as an argument of an aggregate function.

In your case, since c~maktx i.e. Material Description is going to be same for each material, it should also be included in the GROUP BY clause. Also, all other columns not part of an aggregate function (in your case, all columns) should be part of GROUP BY clause.

Regards,

Ravi

Re: group by syntax does not work in inner join statement

siongchao_ng — Thu, 20 Jun 2013 02:33:33 GMT

Hi Ravi,

Thanks for your reply. Means I have to select all the fields specified in my select statement? After I add in the c~maktx, it came out error again asking to include the other fields as well. So the statement without error will be:

Re: group by syntax does not work in inner join statement

arindam_m — Thu, 20 Jun 2013 03:47:01 GMT

Hi,

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. In your case if you use all the fields selected in the GROUP BY clause. The Select statement might as well be like

SELECT a~charg
          a~matnr
          a~werks
          a~menge
          a~kdauf
          a~shkzg
          b~matkl
          c~maktx
   INTO CORRESPONDING FIELDS OF TABLE lt_product
   FROM aufm AS a
   INNER JOIN mara AS b
   ON a~mandt = b~mandt
   AND a~matnr = b~matnr
   INNER JOIN makt AS c
   ON b~mandt = c~mandt
   AND b~matnr = c~matnr
   WHERE a~charg IN s_batch
   AND   a~werks EQ p_plant
   AND ( a~bwart = '101' OR a~bwart = '102' ).

as you are not doing an aggregate function use.

Cheers,

Arindam

Re: group by syntax does not work in inner join statement

raviahuja — Thu, 20 Jun 2013 04:12:37 GMT

Yes. But ideally that is equivalent of not adding GROUP BY clause at all. Since GROUP BY is normally used for aggregate functions, if no aggregate functions is used in SELECT statement, its better don't use GROUP BY clause at all.

Regards,

Ravi

Re: group by syntax does not work in inner join statement

siongchao_ng — Thu, 20 Jun 2013 06:10:22 GMT

Hi Guys,

I'll sum up menge field then in order to use the group by. I can exclude the menge field. That shall work.

Correct statement will be:

   SELECT a~charg
         a~matnr
         a~werks
         sum( a~menge )
         a~kdauf
         a~shkzg
         b~matkl
         c~maktx
INTO CORRESPONDING FIELDS OF TABLE lt_product
FROM aufm AS a
INNER JOIN mara AS b
ON a~mandt = b~mandt
AND a~matnr = b~matnr
INNER JOIN makt AS c
ON b~mandt = c~mandt
AND b~matnr = c~matnr
WHERE a~charg IN s_batch
AND   a~werks EQ p_plant
AND a~bwart IN ('101', '102' )
GROUP BY a~charg a~matnr a~werks c~maktx b~matkl a~shkzg a~kdauf.

Re: group by syntax does not work in inner join statement

Former Member — Thu, 20 Jun 2013 09:24:37 GMT

hi Siong Chao Ng,

do this way

SELECT a~charg

a~matnr

a~werks

sum( a~menge ) as menge

a~kdauf

a~shkzg

b~matkl

c~maktx

INTO CORRESPONDING FIELDS OF TABLE lt_product

FROM aufm AS a

INNER JOIN mara AS b

ON a~mandt = b~mandt

AND a~matnr = b~matnr

INNER JOIN makt AS c

ON b~mandt = c~mandt

AND b~matnr = c~matnr

WHERE a~charg IN s_batch

AND a~werks EQ p_plant

AND ( a~bwart = '101' OR a~bwart = '102' )

GROUP BY a~charg a~matnr a~werks a~shkzg b~matkl a~kdauf c~maktx.