Re: Group_Concat help...
;>>> 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_ipsconnections --- --- - 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
RE: Group_Concat help...
> -Original Message- > From: Mihail Manolov [mailto:mihail.mano...@liquidation.com] > Sent: March 19, 2012 12:44 PM > To: Steven Staples > Cc: > Subject: Re: Group_Concat help... > > Try this > > SELECT `user_id`, `login_ip`, > 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`, `login_ip` > HAVING COUNT(`id`) > 2 > ORDER BY COUNT(`id`) DESC > LIMIT 0, 15; > > On Mar 19, 2012, at 12:06 PM, Steven Staples wrote: > > > 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; > Thanks for the reply Mihail. I was initially doing it that way, but if you look at the example of what I wanted as a reply, it doesn't work. I was hoping for a result that I could just plop into the PHP code, and I wouldn't have to manipulate it at all, but so far, it looks like I am going to have to do that... unless anyone else here has another idea... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Group_Concat help...
Try this SELECT `user_id`, `login_ip`, 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`, `login_ip` HAVING COUNT(`id`) > 2 ORDER BY COUNT(`id`) DESC LIMIT 0, 15; On Mar 19, 2012, at 12:06 PM, Steven Staples wrote: > 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; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
