(Mysql 3.23] Query with SUM 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 :D

[Aggro]

tr********@gmai l.com wrote:

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?

Because you make a join. That means that for each row in the first
table, your query will seek a partner from the other table. Without any
where conditions, that would mean that amount of returned rows is
rows_in_first_t able * rows_in_second_ table. Try this query:
SELECT *
FROM Tbl_Country, Tbl_config
WHERE country_id =1;

And you perhaps understand better why you are getting the numbers you
are getting.

Your problem could perhaps be fixed by adjusting the WHERE conditions of
your query, but that would require a knowledge of what you actually want
to get, so I can't give you any clear answer for that one. But perhaps
something like this:

SELECT *
FROM Tbl_Country, Tbl_config
WHERE Tbl_Country.con fig_id=Tbl_conf ig.id AND country_id =1;

[trihanhcie]

Thanks ^^ It's quite logic in fact ;) well I made it more precise and
it's working for the moment... hoping it will still be ok in the future
;)

Thanks again