sheeri kritzer wrote:
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
Thanks for your reply. I am currently using the following query which
is working:
select count(*) from dummy where status = 'Neg'
and not exists (select 1 from dummy where status = 'Pos');
Although I am looking at SGreen's proposed solution to sum up all the
NEGs and POSs and then do a comparison.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
