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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Mar 19
Mihail Manolov Re: Group_Concat help...
Mar 19, 2012; 16:44
Mihail Manolov
Re: Group_Concat help...
Mar 19
Steven Staples RE: Group_Concat help...
Mar 19, 2012; 13:33
Steven Staples
RE: Group_Concat help...
Mar 19
Hal?sz S?ndor Re: Group_Concat help...
Mar 19, 2012; 13:24
Hal?sz S?ndor
Re: Group_Concat help...
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!