Hi, If it didn't existed and was created with the create temporary table it will desapear when you close your corrent session.
otherwise you can drop it with DROP TABLE t_sends; mpneves On Thursday 19 January 2006 16:35, Critters wrote: > Thanks! > I wrote: > > > DELETE FROM t_sends; > > CREATE TEMPORARY table IF NOT EXISTS t_sends > (SELECT f1 as 'domain' from sends WHERE gameID = 1) union all > (SELECT f2 as 'domain' from sends WHERE gameID = 1) union all > (SELECT f3 as 'domain' from sends WHERE gameID = 1) union all > (SELECT f4 as 'domain' from sends WHERE gameID = 1) ; > > SELECT MID(domain,INSTR(domain,'@')+1) AS 'domain' , count(*) from t_sends > GROUP by 'domain' > > > And that does the trick > > Is there any way to destroy the "t_sends" table? > -- > Dave > > > ----- Original Message ----- > From: "Marco Neves" <[EMAIL PROTECTED]> > To: "Critters" <[EMAIL PROTECTED]> > Cc: <mysql@lists.mysql.com> > Sent: Thursday, January 19, 2006 4:20 PM > Subject: Re: Group By over many colums > > > Hi Critters, > > The problem is that as your MySQL is 4.0.21 don't suport the subselect you > would need to do the group. > > I was thinking and you have another alternative: > > CREATE TEMPORARY table tdata > (SELECT f1 as 'domain' from sends) union all > (SELECT f2 as 'domain' from sends) union all > (SELECT f3 as 'domain' from sends) union all > (SELECT f4 as 'domain' from sends); > > -- This would create an temporary table with all the data > > SELECT domain,count(*) from tdata GROUP by domain; > > -- This sould do the trick. > > mpneves > > On Thursday 19 January 2006 16:14, Critters wrote: > > Thanks for the replies Marco... > > mysql Ver 12.22 Distrib 4.0.21 > > > > So that could be it? > > > > By the way... > > > > (SELECT f1 as 'domain' from sends) union > > (SELECT f2 as 'domain' from sends) union > > (SELECT f3 as 'domain' from sends) union > > (SELECT f4 as 'domain' from sends) > > > > Works, and returns a list where f1, f2, f3 and f4 are all in the column > > "domain" > > > > But as soon as I add "GROUP BY domain" to the end it fails. I have also > > tried "GROUP BY 'domain'" > > -- > > David Scott > > -- > AvidMind, Consultadoria Informática, Unipessoal, Lda. > Especialistas em OpenSource > http://www.avidmind.net > OBC2BIP -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]