COUNTs don't add up 
Author Message
 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

message can get through to the mailing list cleanly



Sat, 10 Jan 2004 04:14:02 GMT
 COUNTs don't add up

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

Thanks!

Alex

Quote:

> Could species2 be null?  If so, I don't think those rows would appear when you
> test for <> 'somevalue'.


> 03:32:04 PM

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Sat, 10 Jan 2004 04:33:42 GMT
 COUNTs don't add up
hi ,
try this :
select species2, count(*) from homol_loclink group by species2;

it will show you.
Regards
Omid

Quote:


>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

>message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------



Sat, 10 Jan 2004 04:41:20 GMT
 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

Quote:
----- Original Message -----


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

> message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Sat, 10 Jan 2004 04:50:01 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Parallel SQL and Access back ends record counts don't match

2. insert/update/select in one query, but don't show the record count

3. triggers don't get exported, dba jobs don't run

4. Don't duplicate v Don't resend trade-off theory

5. alter table,add column puts a default constraint that I don't want

6. creating multiple incremental b'ups

7. IndexDefs.Add('Help','Please',[ixD2WorksGreat, ixD3Errors])

8. Don't read this - it's a test

9. Replication Job ID's don't match

10. Simple cluster index question (sorry, I'd RTFM, but don't hav

11. Confirmed D2 bug: cached updates don't work for master/detail - here's why

12. Don't Miss ODTUG's Spring Conference, June 2-5, Palm Springs, CA


 
Powered by phpBB® Forum Software