Summing a field in a Joined Table

[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.

Note: I need the total of the entire TableA.Amount field, so GROUP BY TableA.ID won't work for me, since that will give me the Sum By ID and not in total!!

Thanks in advance for any help,
mmarseil

 

[aalmeida]

try to use a where clause as:
SELECT Sum(TableA.Amount), Sum(TableB.Amount) FROM
TableA LEFT JOIN TableB ON TableA.ID = TableB.LinkID
where TableB.Amount is null

let me know AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[mmarseil]

Sorry my fault very bad example!! Here is my the way my data is set up:

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

TableB:
MYID Amount
2 10
2 20
3 10

TableC:
MYID Amount
1 50
2 20
3 60

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 40

which is incorrect!!

Sorry again for the bad example earlier!!

 

[tlbroadbent]

Create two sub queries and JOIN them for the final result.

Select qryB.SumB, qryC.SumC
From
(Select MyID, Sum(Amount) As SumB
From TableB Inner Join TableA
On TableB.MyID=TableA.ID
Where TableA.RndFld='B'
Group By MyID) As qryB

Inner Join

(Select MyID, Sum(Amount) As SumC
From TableC Inner Join TableA
On TableC.MyID=TableA.ID
Where TableA.RndFld='B'
Group By MyID) As qryC

On qryB.MyID=qryC.MyID
Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm