List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:February 13 2014 2:30am
Subject:Re: Narrowing a SELECT statement by multiple hits
View as plain text  
On Wed, Feb 12, 2014 at 7:35 PM, Jennifer <jennifer@stripped> 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`
Thread
Narrowing a SELECT statement by multiple hitsJennifer13 Feb 2014
  • Re: Narrowing a SELECT statement by multiple hitsLarry Martell13 Feb 2014
    • Re: Narrowing a SELECT statement by multiple hitsJennifer17 Feb 2014
      • Re: Narrowing a SELECT statement by multiple hitsRoy Lyseng17 Feb 2014
        • Re: Narrowing a SELECT statement by multiple hitsJennifer17 Feb 2014
          • Re: Narrowing a SELECT statement by multiple hitsRoy Lyseng17 Feb 2014
            • Re: Narrowing a SELECT statement by multiple hitsJennifer18 Feb 2014
          • Re: Narrowing a SELECT statement by multiple hitsshawn l.green17 Feb 2014