Jay,

I'm pretty sure you can't do this with a single query. The root problem becomes evident when you remove the sum() and group by to see the real cursor you are working on:

SELECT Cust.custid, Cust.custname, orders.amt, pay.payamt ;
FROM cust ;
Inner Join orders on Cust.custid = Orders.custid ;
Inner Join pay on cust.custid = pay.custid

This ends up joining every payment row to every order row for a customer. I'd probably actually do this with three queries:

select custid, sum(amt) as totalamt ;
   from orders ;
   into cursor allorders ;
   group by custid

select custid, sum(pay) as totalpay ;
   from pay ;
   into cursor allpayments ;
   group by custid

select cust.custid, custname, totalamt, totalpay ;
   from cust ;
   left outer join allorders
      on cust.custid = allorders.custid ;
   left outer join allpayments
      on cust.custid = allpayments.custid ;
   into cursor TheResult

This will also ensure that you cover the cases where payments have been made with no orders (as unlikely as that is) and orders where no payments have been made.


>I am trying to create one view which sums orders and payments by customer. I have three tables; cust, orders and pay and they are linked by custid. All customers have at least one order and one payment.
>I get incorrect results with the following code:
>
>SELECT Cust.custid, Cust.custname, sum(orders.amt), sum(pay.payamt);
> FROM cust ;
> Inner Join orders on Cust.custid = Orders.custid ;
> Inner Join pay on cust.custid = pay.custid;
> GROUP BY Cust.custid
>
>
>The following, however, does work correctly, but I want it in one view.
>
>SELECT Cust.custid, Cust.custname, sum(orders.amt);
> FROM cust ;
> Inner Join orders on Cust.custid = Orders.custid ;
> GROUP BY Cust.custid;
> into Cursor temp
>
>Select temp.*, sum(pay.payamt);
>From Temp;
>Inner Join pay on Temp.custid=pay.custid;
>Group by Temp.custid
>
>Can anyone explain what is going on and perhaps provide some help?