SQL: SELECT SUM giving weird results.

Here's a strange problem (at least to me it's strange). First of all I'm using Access 2000 for the time being to give a customer a proof of concept.

Let's say I have 2 tables: TableA, TableB
TableA has two fields: ID, Price
TableB has two fields: ID, Cost

Data in TableA
ID|Price
1|2
2|3
3|4

Data in TableB
ID|Cost
1|1
2|2

SELECT SUM([TableA].[Price]) As [TPrice] FROM [TableA]
Returns [TPrice] with a value of 9. That makes sense.

SELECT SUM([TableB].[Cost]) As [TCost] FROM [TableB]
Returns [TCost] with a value of 3. That makes sense too.

SELECT SUM([TableA].[Price]) AS [TPrice],SUM( [TableB].[Cost]) AS [TCost] FROM [TableA], [TableB]
Returns [TPrice] with 18 and [TCost] with 9.:confused: :confused: :confused: It looks like it's multiplying the SUM value by the number of records in the other table.

Does anyone know why this is happening or what I'm doing wrong?



Thanks,

Leecher

I believe that is the default operation when joining or not joining while doing multiple aggregate SQL functions.

My advice... do it in 2 seperate SQL queries.

Since you din't specify any conditions for the join, it first creates the table as follows

Table A Price|Table B Cost
2|1
2|2
3|1
3|2
4|1
4|2

It's like an OUTER JOIN. Every item in A is paired with each item in B.

Is there a way to get the correct sum figures in one query?

Quote:

---

Originally posted by Leecher
Is there a way to get the correct sum figures in one query?

---

no