GROUP by aggregation does not calculate SUM

Question

I have to aggregate in my query SUM of AMUNT field according to WERKS, DATUM and UZEIT I try to make a group by without any success I have an error like that:

What is the problem in my code?

That is my ABAP code:

 DATA: gt_compr TYPE TABLE OF yrt_h_sales
 SELECT    werks, extnb, datum, uzeit, sumvt, deprt, dpext, SUM( amunt ) AS amunt
  INTO      CORRESPONDING FIELDS OF TABLE @gt_compr
  FROM      yrt_h_sales 
  WHERE     werks IN @so_werks
  AND       datum IN @so_datum
  GROUP BY  werks, datum, uzeit.

After I corrected it and I did this, the code looks as follows:

 SELECT    werks,  datum, uzeit, extnb, deprt, dpext, SUM( amunt ) AS amunt
  INTO      CORRESPONDING FIELDS OF TABLE @gt_compr
  FROM      yrt_h_sales
  WHERE     werks IN @so_werks
  AND       datum IN @so_datum
  GROUP BY  werks, datum, uzeit,  extnb, deprt, dpext.

So I don't have the compilation error anymore but the aggregation is still not working! I have a 43 line result without sum on the AMUNT column

P.S. this is the structure of my table:

Have you tried specifying the key columns first? Apparently `GROUP BY` order does [matter](https://archive.sap.com/discussions/thread/1858633). — , Feb 15 '17 at 16:41
Yes i tried but it not working, I think the problem is because the other column not in the group by aggregation. For example if made just SELECT werks, datum, uzeit, SUM( amunt ) AS amunt It's working — shmoolki, Feb 15 '17 at 16:48
Please add the table structure of `yrt_h_sales` or open the crystal-ball system connection so that we can take a look ourselves.... — vwegert, Feb 15 '17 at 16:56
https://en.wikipedia.org/wiki/Crystal_ball There is not enough information to answer so we need some magic to get more details. — knut, Feb 15 '17 at 19:49
In what way it does not work? Can you send some data sample of the records of your table to point out, where exactly the summing fails? The query looks OK, so I think the problem is somewhere else or there is even no problem at all. Anyway if you want to group by only WERKS, DATUM, UZEIT and at the same time want to show the other columns of the primary key, then the problem is not in the coding, it is somewhere in your logic because such a construct does not have any sense and is semantically incorrect. — Jagger, Feb 16 '17 at 06:53
You are grouping by all the key fields in your table so there effectively will be no aggregation. Do as the answer from @vwegert suggests and add aggregators for the three fields you do not want to group by, e.g. MIN(EXTNB) etc. — Gert Beukema, Feb 16 '17 at 13:55

score 7 · Accepted Answer · answered Feb 16 '17 at 06:56

Your observation is consistent with the documentation (and what I have so far seen in any other RDBMS I've worked with):

If aggregate expressions are used, any column identifiers that are not included as arguments of an aggregate function must be included after the addition GROUP BY.

Take for example the time field UZEIT: You can tell the system to aggregate (in your case, sum up) all amounts for the same point in time by adding it to the GROUP BY clause, or you can apply an aggregate function as well (SUM would not make any sense here, but MIN might), or you could omit the field altogether. You can not leave it dangling around without further specification - the field either needs to be part of the new key set created by GROUP BY or has to have an aggregate function applied to it so that the system knows what to do with multiple datasets that might occur in the group.

(This is basic SQL btw and not ABAP-specific knowledge.)

score -2 · Answer 2 · answered Apr 20 '21 at 14:16

-2

remove the CORRESPONDING FIELD OF and just place results INTO TABLE

answered Apr 20 '21 at 14:16

Richard Contreras

1

GROUP by aggregation does not calculate SUM

2 Answers2