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
----- Original Message -----
From: "Marco Neves" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Cc: "Critters" <[EMAIL PROTECTED]>
Sent: Thursday, January 19, 2006 3:34 PM
Subject: Re: Group By over many colums
Hi,
To this on I just see a solution, that depends on sub-selects, so it's
available from Mysql 4.1 forward:
SELECT name,count(*) from ((SELECT name1 name FROM <tablename>) UNION ALL
(SELECT name2 name FROM <tablename>) UNION ALL (SELECT name3 name FROM
<tablename>)) tab GROUP by name;
Hope this solves you problem.
mpneves
On Thursday 19 January 2006 15:16, Critters wrote:
Hi
I have a table setup like this:
id, name1, name2, name3
Which has data like this:
1, Dave, Bob, Simon
2, Joe, Tim, Dave
3, Dave, Bob, Tom
I can run
SELECT name, count(id) FROM <tablename> GROUP BY name1 ORDER BY count(id)
DESC
Which would give me:
Dave, 2
Joe, 1
But how would I go about getting the following result:
Dave, 3
Bob, 2
Tom, 2
Joe, 1
Simon, 1
Where it groups by name1, name2 and name3? Is it possible?
-
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]
