[mysql 3.23] Sum in Queries wrong

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

trihanhcie@gmail.com - 21 Apr 2006 11:29 GMT

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 :D

Reply to this Message

John Spencer - 21 Apr 2006 13:15 GMT

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.

> Hi,
>
[quoted text clipped - 13 lines]
>
> Thanks :D

Reply to this Message