Re: it's not NULL, then what is it?

Hi Osvaldo,

> Try:
> SELECT count(*) FROM gallo.sds_seq_reg_shw;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;

> Don't use count(maf), use count(*).

Indeed!

canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw;
count
---------
4645647
(1 row)

canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
count
---------
4578363
(1 row)

canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;
count
-------
67284
(1 row)

$ dc
4578363 67284 + p q
4645647
$

Many thanks, Osvald.

Regards,

Tena Sakai
tsakai(at)gallo(dot)ucsf(dot)edu

-----Original Message-----
From: Osvaldo Kussama [mailto:osvaldo(dot)kussama(at)gmail(dot)com]
Sent: Tue 6/30/2009 6:49 PM
To: Tena Sakai
Subject: Re: [SQL] it's not NULL, then what is it?

2009/6/30 Tena Sakai <tsakai(at)gallo(dot)ucsf(dot)edu>:
> Hi Everybody,
>
> I have a table called gallo.sds_seq_reg_shw,
> which is like:
>
>   canon=# \d gallo.sds_seq_reg_shw
>        Table "gallo.sds_seq_reg_shw"
>         Column      |  Type   | Modifiers
>   ------------------+---------+-----------
>    name             | text    |
>    response         | text    |
>    n                | integer |
>    source           | text    |
>    test             | text    |
>    ref              | text    |
>    value            | real    |
>    pvalue.term      | real    |
>    stars.term       | text    |
>    gtclass.test     | text    |
>    fclass.test      | text    |
>    gtclass.ref      | text    |
>    fclass.ref       | text    |
>    markerid         | integer |
>    maf              | real    |
>    chromosome       | text    |
>    physicalposition | integer |
>    id               | text    |
>    ctrast           | text    |
>
> I am intereseted in the column maf (which is real):
>
>   canon=# select maf from gallo.sds_seq_reg_shw
>   canon-#  order by maf asc;
>        maf
>   -------------
>    0.000659631
>    0.000659631
>    0.000659631
>    0.000659631
>         .
>   (trunacated for the interest of breivity)
>         .
>
> Another way to look at this column is:
>
>   canon=# select maf from gallo.sds_seq_reg_shw
>   canon-#  order by maf desc;
>        maf
>   -------------
>
>
>
>         .
>   (trunacated for the interest of breivity)
>         .
>
> These rows shown are blanks, as far as I can tell.
> But...
>
>   canon=# select count(maf) from gallo.sds_seq_reg_shw;
>    count
>   -------
>    67284
>   (1 row)
>
>   canon=# select count(maf) from gallo.sds_seq_reg_shw
>   canon-#  where maf ISNULL;
>    count
>   -------
>        0
>   (1 row)
>
>   canon=#
>   canon=# select count(maf) from gallo.sds_seq_reg_shw
>   canon-#  where maf NOTNULL;
>    count
>   -------
>    67284
>   (1 row)
>
> My confusion is that if they are real and not null,
> what are they?  How would I construct a query to do
> something like:
>
>  select count(maf)
>    from gallo.sds_seq_reg_shw
>   where maf ISBLANK;
>

Try:
SELECT count(*) FROM gallo.sds_seq_reg_shw;
SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;

Don't use count(maf), use count(*).

Osvaldo