group by syntax does not work in inner join statement

[siongchao_ng]

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.

[raviahuja]

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

[siongchao_ng]

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:

   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 c~maktx b~matkl a~shkzg a~kdauf a~menge.

[arindam_m]

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

[raviahuja]

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

[siongchao_ng]

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.

[Former Member]

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.