RE: Group By over many colums
I would suggest a union SELECT name, count(*) FROM (SELECT name1 as name from mytable union select name2 as name from mytable union select name3 as name from table) GROUP BY name but perhaps there's a better way... Regards, Patrick -Original Message- From: Critters [mailto:[EMAIL PROTECTED] Sent: Thursday, 19 January 2006 16:17 To: mysql@lists.mysql.com Subject: Group By over many colums 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: Group By over many colums
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]
Re: Group By over many colums
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]
Re: Group By over many colums
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]
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]
Re: Group By over many colums
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]
Re: Group By over many colums
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]
