Subject: COUNTs don't add up

From: Alexander Turchin <[email-masked]>
Date: Mon, 23 Jul 2001 15:32:04 -0400

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)

---

From: "Len Morgan" <[email-masked]>
Date: Mon, 23 Jul 2001 15:37:55 -0500

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" <[email-masked]>
To: "PostgreSQL Mailing List" <[email-masked]>
Sent: Monday, July 23, 2001 2:32 PM
Subject: [GENERAL] COUNTs don't add up

> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [email-masked] so that your
> message can get through to the mailing list cleanly
>