470,590 Members | 2,556 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,590 developers. It's quick & easy.

(Mysql 3.23] Query with SUM wrong

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

Apr 21 '06 #1
2 1349
tr********@gmail.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_table * 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.config_id=Tbl_config.id AND country_id =1;
Apr 21 '06 #2
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

Apr 21 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Kamil | last post: by
reply views Thread by Lenz Grimmer | last post: by
reply views Thread by Richard Gabriel | last post: by
reply views Thread by Henry Hank | last post: by
3 posts views Thread by Juan Antonio Villa | last post: by
3 posts views Thread by Me Alone | last post: by
30 posts views Thread by Einstein30000 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.