The actual table is called "sends" and the data is like this:
| id | f1 | f2 | f3
|
| 3 | foo.com | yahoo.com | |
| 4 | dsl.pipex.com | foo.com | foo.com |
| 5 | vodafone.com | btinternet.com | co-op.co.uk |
I tired:
SELECT domain, count(*)
FROM (
(SELECT f1 as domain from sends) union all
(SELECT f2 as domain from sends) union all
(SELECT f3 as domain from sends)
)
GROUP BY domain
But I get:
[localhost] ERROR 1064: You have an error in your SQL syntax. Check the
manual that corresponds to your MySQL
server version for the right syntax to use near 'SELECT f1 as domain from
sends) union all (SELECT f2 as domain
Can you spot where I am going wrong?
-
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]
