count() problem, stupid question inside

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

 

What's the new structure?