(Mysql 3.23] Query with SUM wrong
am 21.04.2006 12:31:15 von 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
Re: (Mysql 3.23] Query with SUM wrong
am 21.04.2006 12:52:16 von Aggro
trihanhcie@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;