Message-ID: <006901c113b7$5af91220$0908a8c0@bstx.cc>
From: "Len Morgan" <len-morgan@crcom.net>
To: "Alexander Turchin" <aturchin@chip.org>,
	"PostgreSQL Mailing List" <pgsql-general@postgresql.org>
References: <3B5C7BB4.A69E20DE@chip.org>
Subject: Re: COUNTs don't add up
Date: Mon, 23 Jul 2001 15:37:55 -0500
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Could there be two rows that have NULL is the value?  They don't "count" as
far as I know.  You can try:

SELECT count(*) from homol_loclink WHERE species2 IS NULL

to verify this.

len morgan

----- Original Message -----
From: "Alexander Turchin" <aturchin@chip.org>
To: "PostgreSQL Mailing List" <pgsql-general@postgresql.org>
Sent: Monday, July 23, 2001 2:32 PM
Subject: [GENERAL] COUNTs don't add up


> Hello all,
>
> I have a database where (I believe; the database was not made by me) one
> of the columns contains only two values: A or B. When I count all the
> rows in the database I get X; when I count the rows containing A or B in
> that column I get X-2; when I count the rows containing neither A nor B
> I get 0. Any explanation (hopefully, again, I am doing something wrong
> :)?
>
> The actual queries are found below.
>
> Thanks!
>
> Alex
>
> aturchin=# SELECT COUNT(*) FROM homol_loclink
> aturchin-# WHERE (species2 = 'Mus musculus') OR
> aturchin-# (species2 = 'Rattus norvegicus');
>  count
> -------
>   7110
> (1 row)
>
> aturchin=# SELECT COUNT(*) FROM homol_loclink;
>  count
> -------
>   7112
> (1 row)
>
> aturchin=# SELECT COUNT(*) FROM homol_loclink
> aturchin-# WHERE (species2 <> 'Mus musculus') AND
> aturchin-# (species2 <> 'Rattus norvegicus');
>  count
> -------
>      0
> (1 row)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>