You could do something similar to the following: set @a:=0; select 0 from Dummy where if(status="Pos",@a:=null,@a:[EMAIL PROTECTED]); select @a;
Use the value of @a -- it will be null if there was ever a null value (because 1+NULL=NULL) and it will be the count if there were no 'Pos' values. So you ignore the result of the select, and use only the value of @a. There's probably a better way to do this query, in which the select returns nothing, but I'm not sure how to do it. Here's how I did it. mysql> select * from Dummy; +----+--------+ | id | status | +----+--------+ | 1 | Neg | | 2 | Neg | | 3 | Pos | | 4 | Neg | +----+--------+ 4 rows in set (0.00 sec) mysql> set @a:=0; select 0 from Dummy where if(status="Pos",@a:=null,@a:[EMAIL PROTECTED]); select @a; Query OK, 0 rows affected (0.00 sec) +---+ | 0 | +---+ | 0 | | 0 | +---+ 2 rows in set (0.00 sec) +------+ | @a | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> select * from Dummy2; +----+--------+ | id | status | +----+--------+ | 1 | Neg | | 2 | Neg | | 3 | Neg | | 4 | Neg | +----+--------+ 4 rows in set (0.00 sec) mysql> set @a:=0; select 0 from Dummy2 where if(status="Pos",@a:=null,@a:[EMAIL PROTECTED]); select @a; Query OK, 0 rows affected (0.01 sec) +---+ | 0 | +---+ | 0 | | 0 | | 0 | | 0 | +---+ 4 rows in set (0.00 sec) +------+ | @a | +------+ | 4 | +------+ 1 row in set (0.00 sec) Hope that helps! -Sheeri On 11/23/05, Gobi <[EMAIL PROTECTED]> wrote: > Suppose that I have a dummy table named, Dummy, and it has a column name > Status, which can contain values Neg, or Pos. > > If I have the following data in table, Dummy: > > id, status > 1 Neg > 2 Neg > 3 Pos > 4 Neg > > I need to write a query returns the count of number of Neg in status but > filters out everything even if there is one Pos value in the column. So > in this case, there will be nothing returned since there is Pos in row 3. > > I know I can work backward by selecting on status = Pos and if there is > a record returned, I can dump all the results but I would prefer a > solution that will return nothing if Pos is found in one of the records. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
