List:General Discussion« Previous MessageNext Message »
From:Dave Date:July 7 1999 4:22am
Subject:Multi Table Sums
View as plain text  
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







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