[mysql 3.23] Sum in Queries wrong

[trihanhcie]

Hi,

Well the following query is not the one i will use but it's the same
prob :

SELECT sum(country_id ) , count( country_id )
FROM Tbl_Country, Tbl_config
WHERE country_id =1

Well ...the result should be sum(country_id )= 1 et count( country_id
) = 1... but I have :
sum(country_id )= 4 et count( country_id ) = 4...
The number of entries in Tbl_Config is also 4.... I know that adding
Tbl_config behind is useless but it was just a test... How come i don't
have 1 and 1 as result?

Thanks

 

[John Spencer]

Since you haven't joined the tables to each other you get a cartesian join.
That is a row is generated for every combination of the rows in the two
tables. So if Tbl_Country has 10 rows and tbl_config has 4 rows, you end up
with 40 rows all told (10 * 4). So, you have four rows for each country_id.