Hi All:

I need some help with the following SQL code. I can do what I want in more than
one select statement, but my goal is the most elegant solution.

Consider the two follwing tables:

Table EGGS
Record#  ED_INVNUM  ED_LOADNUM ED_BATNUM       ED_CASES
      1  432059     293        368                29.34
      2  432059     293        376               146.70
      3  432059     293        391                29.34
      4  432059     293        398               123.70
      5  432059     293        398                23.00
      6  432059     293        399                48.00
      7  432059     293        412                40.00
      8  432059     293        415                58.00
      
Table TCHICKS
Record#  HI_INVNUM  HI_LOADNUM HI_BATNUM       HI_AMT
      1  A0017      293        368               9101
      2  A0017      293        376              42413
      3  A0018      293        391               7826
      4  A0018      293        398              42174
      5  A0018      293        398               4280
      6  A0018      293        399              15555
      7  A0018      293        412               9649
      8  A0018      293        415              15516
      9  A0018      293        412               2000
     10  A0018      293        412                702
      

I then run the followng SQL code:

SELECT eggs.ed_invnum, eggs.ed_loadnum, ;
  eggs.ed_batnum, SUM(eggs.ed_cases * 360) as eggs, tchicks.hi_invnum, tchicks.hi_batnum, ;
  SUM(tchicks.hi_amt) as chicks ;
 FROM ;
     eggs ;
    INNER JOIN tchicks ;
   ON  eggs.ed_loadnum = tchicks.hi_loadnum;
   AND  eggs.ed_batnum = tchicks.hi_batnum;
 GROUP BY eggs.ed_invnum, eggs.ed_invdat, eggs.ed_loadnum, eggs.ed_batnum, tchicks.hi_invnum, tchicks.hi_batnum ;
 INTO CURSOR result

And get the following result:

  ED_INVNUM  ED_LOADNUM ED_BATNUM  EGGS HI_INVNUM  HI_BATNUM                 CHICKS
  432059     293        368    10562.40 A0017      368                         9101
  432059     293        376    52812.00 A0017      376                        42413
  432059     293        391    10562.40 A0018      391                         7826
  432059     293        398   105624.00 A0018      398                        92908
  432059     293        399    17280.00 A0018      399                        15555
  432059     293        412    43200.00 A0018      412                        12351
  432059     293        415    20880.00 A0018      415                        15516

Which is obviously wrong, since there should be 46,454 chicks in hi_batnum 398, not 92,908. What the system is doing is SUMming four records instead of two. What's the right way to code this?

Thanks,

Yossi