I have 2 tables:

table1  with fields  lid_ID,amountdue   
table2  with fields  lid_ID,amountpaid

I would like to have an sql that retrieves the SUM(amountdue) from table1 and the SUM(amountpaid) from table2
each grouped by the common field lid_ID

Something like.... but is does not work properly

SELECT a.lid_ID,SUM(a.amountdue),SUM(b.amountpaid) FROM table1 a
JOIN table2 ON (a.lid_ID=b.lid_ID)
GROUP BY a.lid_ID

It likely does exactly what you told it to However that is not what
you want probably.

The issue is the join - you're asking the records to get matched up but
that does not relly make sense since the records as you have are not
related to each other for purposes of join.

For example if table1 has 2 rows for a given userID and table 2 has 1
row for same userID then your output will be 2 rows (all from table 1
and matching record from table 2 for both rows) and sum for table 2 will
be double since table2 value is included for both rows. Of course if you
have more records then you get more rows (for example 3 and 2 rows
result is 6 join rows so now you'er counting table 1 rows twice and
table 2 three times) etc.

You could do something like this

select userID,sum(amountdue) from table1 GROUP BY userID
UNION
select userID,sum(amountpaid) from table2 GROUP BY userID

but that would just have just 2 columns so you'd have to consolidate
this in code.

The only way i can think of achieving this would be to create some temp
in-memory tables for temporary results (the 2 queries from above
example) and then do a final join on them:

select userID,sum(amountdue) as ADue into "\memory\t1data" from table1
GROUP BY userID;
select userID,sum(AmountPaid) as APaid into "\memory\t2data" from table2
GROUP BY userID;
select A.userID,A.ADue,B.APaid from "\memory\t1data" A JOIN
"memory\t2data" B ON A.UserID=B.UserID;

Raul



On 6/25/2012 1:46 PM, Paul Waegemans wrote:
> I have 2 tables:
>
> table1  with fields  lid_ID,amountdue
> table2  with fields  lid_ID,amountpaid
>
> I would like to have an sql that retrieves the SUM(amountdue) from table1 and the SUM(amountpaid) from table2
> each grouped by the common field lid_ID
>
> Something like.... but is does not work properly
>
> SELECT a.lid_ID,SUM(a.amountdue),SUM(b.amountpaid) FROM table1 a
> JOIN table2 ON (a.lid_ID=b.lid_ID)
> GROUP BY a.lid_ID
>