COUNTs don't add up

Yes, that turns out to be exactly the case - should have thought of it myself (of
course :).

Thanks!

Alex

wsheldah@lexmark.com wrote:

hi ,
try this :
select species2, count(*) from homol_loclink group by species2;

it will show you.
Regards
Omid

From: Alexander Turchin <aturchin@chip.org>
To: PostgreSQL Mailing List <pgsql-general@postgresql.org>
Subject: [GENERAL] COUNTs don't add up
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)

---------------------------(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

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

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