[Resolved] Counting distinct entries

[mxpx4]

Hello:

I have a MySQL table which logs visitors' IP addresses when they hit pages on my site. One IP address may hit a page multiple times, and is recorded each time into the table as a separate row.

I would like to find out which page receives the most unique visitors by seeing which has the most unique IP addresses. This is the SQL query I am using:

SELECT DISTINCT ip_address, page_id, count( * ) FROM stats GROUP BY page_id ORDER BY `count( * )` DESC

However, count(*) returns the number of TOTAL rows registered for each distinct page... NOT the number of distinct IP Addresses.

How can I modify my query to count distinct IP Addresses? --Thanks!

[TheIceman5]

$query = SELECT DISTINCT ip_address, page_id FROM stats GROUP BY page_id

$mysql_num_rows($query)

[mxpx4]

This works... but I have several hundred pages and I need to pick the largest number of distinct IP Addresses.

So, I could run this through, but how would I determine what the largest number was?

[TheIceman5]

maybe

SELECT DISTINCT ip_address, page_id, count(page_id) AS TheCount FROM stats GROUP BY page_id ORDER BY TheCount DESC

dunno, sounds good, wether it will work is anothe thing.

[mxpx4]

After a little discovery... you can use a DISTINCT clause in count()!

The query that did the trick:

SELECT DISTINCT ip_address, page_id, count( DISTINCT ip_address) as 'TheCount' FROM stats GROUP BY page_id ORDER BY TheCount DESC

Whoo hoo! Thank you 😛