On Mon, 2004-05-24 at 11:32, John Nichel wrote:
> Rich Allen wrote:
> > iH
> >
> > this should work
> >
> > test> select * from xt;
> > +----+-------+
> > | id | field |
> > +----+-------+
> > | 1 | 0 |
> > | 2 | 0 |
> > | 3 | 7 |
> > | 4 | 8 |
> > | 5 | 7 |
> > | 6 | 0 |
> > | 7 | 6 |
> > | 8 | 7 |
> > | 9 | 8 |
> > +----+-------+
> > 9 rows in set (0.00 sec)
> >
> > test> select count(distinct(field)) + (select count(*) from xt where
> > field=0) - 1 from xt;
> > +----------------------------------------------------------------------+
> > | count(distinct(field)) + (select count(*) from xt where field=0) - 1 |
> > +----------------------------------------------------------------------+
> > | 6 |
> > +----------------------------------------------------------------------+
> > 1 row in set (0.01 sec)
> > note that i subtract one since i counted a "0" value in the distinct
> > part ...
> >
> > - hcir
>
> That's what I needed. Thanks!
Note that this solution will be off by one if there aren't any zeros in
your data. Try this:
select count(IF(field>0,NULL,1)) +
count(distinct IF(field>0,field,NULL))
from test;
--
. Garth Webb
. [EMAIL PROTECTED]
.
. shoes * éå * schoenen * ëí * chaussures * zapatos
. Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑÐÐÐÐ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
