1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

count() problem, stupid question inside

Discussion in 'MySQL' started by falcondriver, Nov 21, 2006.

  1. #1
    Hi, i have a data dump with an url and ip address.
    so far i have this query:
    "SELECT count(ip), ip FROM site_dump GROUP BY ip ORDER BY count(ip) DESC",
    SEMrush
    now i need a list with url, ip, and the number of sites on the same ip listed in this table, something like:

    url - ip - sites
    site1.com - 1.2.3.4 - 2
    site2.org - 5.6.7.8 - 1
    copy.net - 1.2.3.4. - 2
    ...

    any ideas?
    i tried it with join, but i get a wrong result for count(ip) here and the query takes quite a while - am i on the right track here?

    SELECT count( d1.ip ) , d1.ip, d2.url
    FROM site_dump d1
    LEFT JOIN site_dump d2 ON d2.ip = d1.ip
    GROUP BY d1.ip
    ORDER BY count( d1.ip ) DESC
     
    falcondriver, Nov 21, 2006 IP
    SEMrush
  2. kashem

    kashem Banned

    Messages:
    1,250
    Likes Received:
    76
    Best Answers:
    0
    Trophy Points:
    0
    #2
    If i dont misunderstand you,

    I think there is no chance of getting the reult. Becuase a url is unique and it is hosted on the one ip , on the other hand on a ip thre are several url.

    If i misunderstood your need, ignore my comment
     
    kashem, Nov 21, 2006 IP
  3. druidelder

    druidelder Peon

    Messages:
    286
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Do a nested select statment:

    SELECT a.ip, a.url,
    (SELECT COUNT(b.ip) AS Expr1
    FROM site_sump AS b
    WHERE (a.ip = b.ip)) AS ipcount
    FROM site_dump AS a
    ORDER BY ipcount desc
     
    druidelder, Nov 21, 2006 IP
    falcondriver likes this.
  4. falcondriver

    falcondriver Well-Known Member

    Messages:
    963
    Likes Received:
    47
    Best Answers:
    0
    Trophy Points:
    145
    #4
    yeah thanks, this does the trick.
    but its ways to slow with 3k entries, guess i create another table and "cache" the result there until i insert/delete a record...
     
    falcondriver, Nov 21, 2006 IP
  5. druidelder

    druidelder Peon

    Messages:
    286
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    0
    #5
    What type of database are you using? If it is Jet based, then the nested select will give you lower performance. Otherwise, it should give you very good performace.
     
    druidelder, Nov 21, 2006 IP
  6. falcondriver

    falcondriver Well-Known Member

    Messages:
    963
    Likes Received:
    47
    Best Answers:
    0
    Trophy Points:
    145
    #6
    its mysql, but i changed the structure and its much faster now - means i get the results instantly and dont have to wait 10 seconds :)
     
    falcondriver, Nov 21, 2006 IP
  7. druidelder

    druidelder Peon

    Messages:
    286
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    0
    #7
    What's the new structure?
     
    druidelder, Nov 21, 2006 IP