Garth, good catch!
- hcir
mysql
- hcir
On May 24, 2004, at 1:05 PM, Garth Webb wrote:
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;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
