Hi,
Before anything else I would verify that your mysql-server is 4.1 or
superior, as before that MySQL didn't suported sub-selects.
It looks to me that the error should be that.
After that I only spot the missing table alias before the Group by.
mpneves
On Thursday 19 January 2006 16:01, Critters wrote:
> 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
--
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]
