Home > mailing lists

Thread: Inaccurate results

Inaccurate results

From

"Chad Thompson"

Date:

07 February 2003, 16:53:36

I just ran this query on my 7.3 DB through pgAdminII

select count(distinct(l.full_phone),tz.state

from lists l join timezone tz on l.area_code = tz.area_code

group by tz.state

having

count(*) > 250000

and got these results

count state

351222 AZ

707618 CA

406669 FL

469797 GA

205115 MD ***

1008549 TX

199121 UT ***

I also tried

having count(*) > '250000',

thinking it might have been a type conversion error.

Why would these ( *** ) have showed up in the list?

Thanks

Chad

Re: Inaccurate results

From

greg@turnstep.com

Date:

07 February 2003, 17:21:02

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


[simplified]
> select count(distinct(full_phone),state
> from lists group by state
> having count(*) > 250000
> ...
> 199121    UT  ***
> ...
> Why would these ( *** ) have showed up in the list?

You are grouping on the *distinct* phones, but running
the HAVING filter on the total counts. Utah has over
250,000 phones, but only 199,121 distinct ones.
In other words, your HAVING clause needs to match your
initial COUNT clause:

having count(distinct(full_phone)


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302071417

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+RAbLvJuQZxSWSsgRAv8hAKD57KIzlbu3WT7VaWAkwA+W8Y/siACfcKJ5
PnJXEfSM2I3GvJ7Dg9I7sO0=
=VXd4
-----END PGP SIGNATURE-----