Re: Group_Concat help...

2012-03-19 Thread Mihail Manolov 
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

RE: Group_Concat help...

2012-03-19 Thread Steven Staples 
 -Original Message-
 From: Mihail Manolov [mailto:mihail.mano...@liquidation.com]
 Sent: March 19, 2012 12:44 PM
 To: Steven Staples
 Cc: mysql@lists.mysql.com
 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...

2012-03-19 Thread Hal�sz S�ndor 
; 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