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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
