List:General Discussion« Previous MessageNext Message »
From:(Hal Date:March 19 2012 6:24pm
Subject:Re: Group_Concat help...
View as plain text  
;>>> 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'

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