List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 15 1999 8:29pm
Subject:Re: Multi Table Sums
View as plain text  
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.

Thread
Multi Table SumsDave7 Jul
  • Re: Multi Table SumsChristian Mack16 Jul