SQL: SELECT SUM giving weird results.

[Leecher]

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

[ober0330]

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.

[leinad31]

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.

[Leecher]

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

[jhermiz]

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

no