Problem Summing Info On Joined Tables

[mmarseil]

Hi there,

What I want is to obtain summary information only on data in
Table A, regardless of any joins with other tables. Normally
if you say for example

SELECT Sum(TableA.Amount), Sum(TableB.Amount) FROM
TableA LEFT JOIN TableB ON TableA.ID = TableB.LinkID,

you will get NOT the sum the TableA.Amount as such, but
rather a sum of the data in TableA where each record
is duplicated for every matching record in TableB. Thus,
say the data is like:

TableA

ID Amount
1 10
2 20
3 30

TableB

LinkID Amount
1 10
1 10
1 10
2 10

The result of the above SELECT statement will be

Sum(TableA.Amount) Sum(TableB.Amount)

80 40

80 is (10 + 10 + 10) + 20 + 30,

the 10 being repeated 3 times, as TableB as three records
which match with the first record in TableA.

So, HOW do I get the absolute sum of the Amount in TableA,
within one SQL statement? Or must you use two SQL statements,
which is what I'm doing currently.

Thanks in advance for any help,
mmarseil

 

[hsitz]

Try this:

SELECT TableA.ID, Sum(([TableA].[Amount])) AS SumA, Sum(([TableB].[Amount])) AS SumB
FROM TableA INNER JOIN TableB ON TableA.ID = TableB.linkid
GROUP BY TableA.ID;

I think that will give you what you want. -- Herb

 

[mmarseil]

I actually tried your solution, however I need to sum the whole Amount column, so Group By TableA.ID doesn't work for me!! Any other thoughts?!!!

 

[hsitz]

This will give result recordset that has a single recordset with two columns, summing like you want:

SELECT Sum(([TableA].[Amount])) AS Expr1, Sum(([TableB].[Amount])) AS Expr2
FROM TableA INNER JOIN TableB ON TableA.ID = TableB.linkid
WHERE (((TableA.ID)>0));

If that is what you're looking for then you've got me wondering why you're interested in getting things done in a single query. I don't see the downside to using two SQL statements. Hmmm...

You could just use two dsum statements or two SQL select sum query strings and stick one of them in wherever you needed to access either of the column totals. Why would you not want to do that? -- Herb

 

[hsitz]

Actually, I think your original query:

SELECT Sum(TableA.Amount), Sum(TableB.Amount) FROM
TableA LEFT JOIN TableB ON TableA.ID = TableB.LinkID,

will work fine, if you just replace 'LEFT JOIN' with 'INNER JOIN'. -- Herb

 

[mmarseil]

Hsitz, thanks for the reply!!

Sorry my fault very bad example, I tried to simplify the problem and I screwed up the reason my query is struggling!! Here is the way my data is set up:

TableA:
MYID RandFld
1 A
2 B
3 A
4 B
5 D

TableB:
MYID Amount
2 10
2 20
3 10

TableC:
MYID Amount
1 50
2 20
3 60
4 10

So, this is my query which is summing duplicate records.

Select SUM(TableB.Amount) As Expr1, SUM(TableC.Amount) As Expr2 From (TableB RIGHT JOIN TableA ON (TableB.MYID = TableA.MYID)) LEFT JOIN TableC ON (TableC.MYID = TableA.MYID) Where TableA.RandFld = 'B'

My Result Set Is:
Expr1 Expr2
----- -----
30 50

which is incorrect!!

The True Answer Should Be:
Expr1 Expr2
----- -----
30 30

which is incorrect!!

Sorry again for the bad example earlier!! As you can see, I can't use INNER JOIN because a MYID may be found in Table B, but not in Table C and I need all included. Thanks again!!

 

[hsitz]

Ohh.. Here's another try:

SELECT DISTINCT (SELECT Sum([Amount]) AS Expr1
FROM tablea INNER JOIN tableb ON tablea.MYID = tableb.myid WHERE tableA.randfld = "B" AS Expr1, (SELECT Sum([Amount]) AS Expr1
FROM tablea INNER JOIN tablec ON tablea.MYID = tablec.myid WHERE tablea.randfld="B" AS expr2
FROM tablea
WHERE (((tablea.RandFld)="B&quot);


The above query is really sort of cheating, because it just puts two subqueries into a main query. But it does the job, and it's in one query!