(Mysql 3.23] Query with SUM wrong

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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

Re: (Mysql 3.23] Query with SUM wrong

trihanhcie@gmail.com wrote:
Quoted text here. Click to load it

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_table * rows_in_second_table. Try this query:

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:

FROM Tbl_Country, Tbl_config
WHERE Tbl_Country.config_id=Tbl_config.id AND country_id =1;

Re: (Mysql 3.23] Query with SUM wrong

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

Site Timeline