Any mySQL experts around?
 

I have this mySQL query that displays raw page visits.

SELECT DISTINCT page_visited, COUNT(page_visited) FROM clubd_counter_data GROUP BY page_visited ORDER BY page_visited

The table has a field called surfer_ip that holds the ip of the surfer that visited a page.

Now what I want to do is display unique ip vists per page. What do I need to add to the above SQL query to get that? I want the count to be unique page visits instead of all page visits.

Hugs,
Danielle

SELECT COUNT(DISTINCT surfer_ip) FROM clubd_counter_data WHERE table_that_holds_the_page LIKE 'index.hml'

Change "table_that_holds_the_page" to the correct table name that has the page name and "index.html" to the page you want to count unique IP's on.

select count(distinct surfer_ip), page_visited from clubd_counter_data group by page_visited

Should aughta do the trick.

BTW - In your original query:

SELECT DISTINCT page_visited, COUNT(page_visited) FROM clubd_counter_data GROUP BY page_visited ORDER BY page_visited

You don't need the 'DISTINCT' keyword for page_visited, as it'll be made effectively distinct in GROUP BY. I suspect mysql would probably just optimize that away however.

HTH. HND. :)

Thanks! This one looks like it returned what I want.

Hugs,
Danielle

Thank you. I am just starting to learn mySQL. I did not know that.

Hugs,
Danielle

No problem. SQL's pretty easy to do once you get the hang of it... the trick of it is just learning how to organize your data and their relationships so you don't bring your server to its knees when checking on some random statistic. ;)

Gimmie a poke at ICQ:4435025 if you need any more help. Except not for a week after this monday... my sql'ing has bought me a trip to jamaica. :thumbsup