Page 2 of 2 First 12
  • Jump to page:
    #16
  1. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    Kinda deviating away from the MySQL theme now...

    If you can't trust the IP address (you generally can for a session, which may constitute a "visitor") and you can't use/trust the email address then you'll have to come up with something else. Like a tracking cookie.
  2. #17
  3. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,110
    Rep Power
    4103
    If you're looking for unique visitors and the only info you have to go on is IP/Email then probably the most accurate would be something like a count of unique emails + a count of IP's that are not associated with any of those emails. Something like this:
    Code:
    SELECT COUNT(*) FROM (
        SELECT DISTINCT email
        FROM analytics 
        WHERE email IS NOT NULL
    
        UNION ALL
    
    
        SELECT DISTINCT IP
        FROM analytics 
        WHERE
            email IS NULL
            AND IP NOT IN (
                SELECT IP 
                FROM analytics
                WHERE email IS NOT NULL
            )
    )
    It'll probably be far from perfect, but it seems like it'd be the most accurate to me given the limited information.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #18
  5. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,246
    Rep Power
    220
    Originally Posted by kicken
    If you're looking for unique visitors and the only info you have to go on is IP/Email then probably the most accurate would be something like a count of unique emails + a count of IP's that are not associated with any of those emails. Something like this:
    Code:
    SELECT COUNT(*) FROM (
        SELECT DISTINCT email
        FROM analytics 
        WHERE email IS NOT NULL
    
        UNION ALL
    
    
        SELECT DISTINCT IP
        FROM analytics 
        WHERE
            email IS NULL
            AND IP NOT IN (
                SELECT IP 
                FROM analytics
                WHERE email IS NOT NULL
            )
    )
    It'll probably be far from perfect, but it seems like it'd be the most accurate to me given the limited information.

    Every derived table must have its own alias
  6. #19
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by English Breakfast Tea
    Every derived table must have its own alias
    so give it one

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo