Thread: COUNTs don't add up

COUNTs don't add up

From

Alexander Turchin

Date:

23 July 2001, 19:04:45

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)

Re: COUNTs don't add up

From

Alexander Turchin

Date:

23 July 2001, 19:26:38

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

Thanks!

Alex

wsheldah@lexmark.com wrote:

> Could species2 be null?  If so, I don't think those rows would appear when you
> test for <> 'somevalue'.
>
> Alexander Turchin <aturchin%chip.org@interlock.lexmark.com> on 07/23/2001
> 03:32:04 PM

Re: COUNTs don't add up

From

"omid omoomi"

Date:

23 July 2001, 19:34:21

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

Re: COUNTs don't add up

From

"Len Morgan"

Date:

23 July 2001, 19:39:00

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
>