Dave wrote:
>
> Hi all,
>
> I have spent hours working on this query...probably an easy one for most of
> you...
>
> I need to sum records in two tables using a third table as the driver or
> base table (I think that is the correct name)
>
> Logic:
>
> The customers table will ONLY return one row and one field (cust_id) based
> on c.cust_id = 1926
>
> The ar_ledger table will return 0 or more rows and the field "amount" must
> be summed for all the rows it returns.
> The ar_ledger table has a field "cust_id" which must match the one row that
> the customers table returns
>
> The rec_inv table will return 0 or more rows and the field "amount" must be
> summed for all the rows it returns.
> The rec_inv table has a field "cust_id" which must match the one row that
> the customers table returns
>
> I try
>
> select sum(a.amount) as balance, sum(r.amount) as monthly
> from ar_ledger a, rec_inv r, lists.customers c
> where
> r.cust_id = c.cust_id and
> a.cust_id = c.cust_id and
> c.cust_id = 1926
> group by c.cust_id
>
> and I get (it appears) the correct balance and a monthly * about 100
>
> I can't see how it is returning too many rows for the rec_inv. Shouldn't
> rec_inv only have back all rows which match cust_id? and the same for
> ar_ledger?
>
> Is it my group by clause?
>
> Any assistance would be appreciated...
>
> -Dave
Hi Dave
You can't do what you want with one query.
This is, because you JOIN each valid result from ar_ledger with all valid results from
rec_inv.
Therefore the result is unpredictable.
Just split them into two, one for the balance and one for the monthly.
Tschau
Christian
PS: Sorry for the late answer, I was on vacation.
