Andy, Your first suggestion hit the nail right on the head. Using the column number does the trick.
Your second entry was incomprehensible to me, and to mysql as well. It reports an error. (Frankly, if that worked it would be so much magic I'd have to uninstall MySQL for being smarter than me. :-) Thanks a bunch for your time, =Austin > -----Original Message----- > From: Andy Eastham [mailto:[EMAIL PROTECTED] > Austin, > > Try using the column number, rather than alias in the group by clause. > > SELECT IF(t2.status IN(2,3,4), "open", "closed") t2st > FROM tasks t2 > GROUP BY 1 > > Then you'll probably want > SELECT IF(t2.status IN(2,3,4), "open", "closed"), count(*) t2st > FROM tasks t2 > GROUP BY t2st > > Andy > -----Original Message----- > From: Austin Hastings [mailto:[EMAIL PROTECTED] > Sent: 09 March 2004 15:10 > To: [EMAIL PROTECTED] > Subject: Newbie Q: How do I get a COUNT of a computed field? > > 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. > > aTdHvAaNnKcSe > > =Austin > > > > > > > -- > 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]
