Re: COUNTs don't add up

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(at)chip(dot)org>
To: "PostgreSQL Mailing List" <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>