List:General Discussion« Previous MessageNext Message »
From:Steven Staples Date:March 19 2012 4:06pm
Subject:Group_Concat help...
View as plain text  
Hello!

I am trying to do something, but I can't seem to figure out how...

My query is as follows:
SELECT `user_id`, GROUP_CONCAT(DISTINCT `login_ip`) AS 'login_ips',
COUNT(`id`) AS 'connections'
FROM `mysql_test`
WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19
23:59:59'
GROUP BY `user_id`
HAVING COUNT(`id`) > 2
ORDER BY COUNT(`id`) DESC 
LIMIT 0, 15;


This query works, it gives me results like:
user_id  login_ips                    connections  
-------  ---------------------------  -------------
      1  192.168.0.200,192.168.0.201              5


But what I am looking to add, is the number of connections per IP, so it
would look kinda something like this:
user_id  login_ips                            connections  
-------  -----------------------------------  -------------
      1  192.168.0.200 (1),192.168.0.201 (3)              5

Is this possible to do with just 1 query?  If so, how would I go about doing
it??


And the table schema:
CREATE TABLE `mysql_test` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `user_id` int(11) unsigned NOT NULL,
  `login_datetime` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `login_ip` varchar(15) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

here is the data in my table:
    id  user_id  login_datetime       login_ip       
------  -------  -------------------  ---------------
     1        1  2012-03-19 11:57:38  192.168.0.200  
     2        1  2012-03-19 11:57:40  192.168.0.201  
     3        1  2012-03-19 11:57:42  192.168.0.200  
     4        1  2012-03-19 11:57:43  192.168.0.200  
     5        1  2012-03-19 11:57:45  192.168.0.201  

Thread
Group_Concat help... Steven Staples19 Mar
  • Re: Group_Concat help... Mihail Manolov19 Mar
    • RE: Group_Concat help... Steven Staples19 Mar
  • Re: Group_Concat help... hsv19 Mar