On Wed, Feb 12, 2014 at 7:35 PM, Jennifer <jenni...@superiorshelving.com> wrote: > Hello, > > I have the following SQL statement that I'd like to add to. It's > used to create a report that's emailed to me showing hits to our site that > didn't provide a referrer. However, I only want to report on multiple hits > from the same IP address - not just a single hit by someone. > > How can I add a condition to only show hits by someone who's hit the > site 2 or more times with the same IP? I tried GROUP BY but that didn't > return all the hits - one one per IP. > > SELECT `ip`,`page`,`url`,`time_stamp` > FROM `ip_addresses` > WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND > CURDATE() - INTERVAL 1 SECOND) > AND TRIM(`referrer`) LIKE '' > ORDER BY INET_ATON(`ip`), `time_stamp` > > I hope I'm explaining this correctly.
Try adding a having clause, e.g.: SELECT `ip`,`page`,`url`,`time_stamp` FROM `ip_addresses` WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND CURDATE() - INTERVAL 1 SECOND) AND TRIM(`referrer`) LIKE '' HAVING COUNT(ip) >2 ORDER BY INET_ATON(`ip`), `time_stamp` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
