"Austin Hastings" <[EMAIL PROTECTED]> wrote: > I'm not sure if this is a bug or a user failure, so I'm going to ask here > before doing anything rash. > > I'm trying to classify a single field using the IN() expression into two > groups: > > IF(t2.status IN(2,3,4), "open", "closed") > > I'd like to GROUP those together so I can COUNT them. > > When I try > > SELECT IF(t2.status IN(2,3,4), "open", "closed") t2st > FROM tasks t2 > GROUP BY t2st > > I get an error. > > It occurs to me that this could be > > 1: A defect in the 3.23.57 version I'm using. I'm downloading 4.1.1a as we > speak, to try it in that version. > > If this is so, can anyone who knows about these things tell me if this is > supported in a later version, and if so which version? > > 2: More likely, it's a fault in my (limited) knowledge of SQL. Perhaps it is > necessary to create a temporary table or do something more sinister to get a > count of a computed field. > > If this is the case, can someone knowledgeable in SQL tell me how to go > about it? I've got a fairly small data set to process (the "full" version of > my query filters down the data) so I can do this outside SQL if I have to. > But I wanted to push this onto SQL if I could. >
What error message did you receive? Your query worked fine for me: mysql> create table tasks(status int); Query OK, 0 rows affected (0.00 sec) mysql> insert into tasks values (1),(2),(3),(4),(5); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT IF(t2.status IN(2,3,4), "open", "closed") t2st FROM tasks t2 GROUP BY t2st; +--------+ | t2st | +--------+ | closed | | open | +--------+ 2 rows in set (0.00 sec) mysql> SELECT IF(t2.status IN(2,3,4), "open", "closed") t2st, count(*) FROM tasks t2 GROUP BY t2st; +--------+----------+ | t2st | count(*) | +--------+----------+ | closed | 2 | | open | 3 | +--------+----------+ 2 rows in set (0.00 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
