;>>> 2012/03/19 12:06 -0400, Steven Staples >>>>
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;
....
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??
<<<<<<<<
Yes, but with two levels: the inner level (a virtual table) groups by both
"user_id" and "login_ip", with COUNT; the outer as above, except from the inner
level, not right from table "mysql_test", and with not COUNT but SUM of the
inner COUNTs.
By the way, I would do this:
WHERE DATE(`login_datetime`) = '2012-03-19'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
