Thread: Strange Problem

Strange Problem

From

"Gustavo Ces"

Date:

09 July 2007, 19:41:26

Hi all,

I´ve got a strange problem, hope you could help. I´ve got a table (a ) with n tuples, and two fields , birthplace and birth date. There´s another table ( say b) with m tuples, where i´ve got a lot of polygons ( with their code, same as bithplace). Now i want to make a join, to obtain how many people corresponds to each place-polygon. I write

create table personsbyplace as select a.birthplacecode as code,count(*) as peoplecount from a,b where
a.birthplacecode=b.polygoncode group by birthplacecode

Well, what i can´t understand is why "Select sum(count) from personsbyplace" is > than "a" row number! (n)

In "b" i´ve got all places and "a" table is a list of people, with their place and birth date. It can´t be greater sum(count) than number of persons! Where is the mistake?

Thanks in advance,

Gus

Re: Strange Problem

From

Jim Nasby

Date:

13 July 2007, 15:39:38

On Jul 9, 2007, at 9:22 AM, Gustavo Ces wrote:
> Hi all,
>
>     I´ve got a strange problem, hope you could help. I´ve got a
> table (a ) with n tuples, and two fields , birthplace and birth
> date. There´s another table ( say b) with m tuples, where i´ve got
> a lot of polygons ( with their code, same as bithplace). Now i want
> to make a join, to obtain how many people corresponds to each place-
> polygon. I write
>
> create table personsbyplace as select a.birthplacecode as code,count
> (*) as peoplecount from a,b where
> a.birthplacecode=b.polygoncode group by birthplacecode
>
> Well, what i can´t understand is why "Select sum(count) from
> personsbyplace" is > than "a" row number! (n)
>
> In "b" i´ve got all places and "a" table is a list of people, with
> their place and birth date. It can´t be greater sum(count) than
> number of persons! Where is the mistake?

Probably because there's a duplicated polygonecode (might want to
stick some underscores in the field names so they're easier to read,
btw).

My question is: if you just want a count of people grouped by
birth_place_code, why join to b at all?
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)