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
mysql
- hcir On May 24, 2004, at 9:36 AM, John Nichel wrote:
Hi,
I have a table which I want to select data from (obiviously). In this table, I have a field which is an integer, and defaults to 0. What I would like to do is count all rows in that table which not only equals 0 for the field, but has a distinct value which is greater than 0.
id field 1 0 2 0 3 7 4 8 5 7 6 0 7 6 8 7 9 8
For the above example, my count should return 6. Three zero's count as 3, three seven's count as 1, two eight's count as 1, and one six counts as 1.
I've tried...
SELECT COUNT(*) FROM db.table WHERE ( field = 0 || ( field > 0 && DISTINCT field ) )
But it still returns the count of all the rows.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
