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", 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
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
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
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...
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.
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