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
--
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]
