Any mySQL experts around? - GoFuckYourself.com

Danielle · 02-06-2004, 02:51 AM

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

swedguy · 02-06-2004, 03:00 AM

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.

rickholio · 02-06-2004, 03:06 AM

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

Should aughta do the trick.

rickholio · 02-06-2004, 03:10 AM

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.

Danielle · 02-06-2004, 03:29 AM

Quote:

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

Should aughta do the trick.

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

Hugs,
Danielle

Danielle · 02-06-2004, 03:30 AM

Quote:

Originally posted by rickholio
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.

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

Hugs,
Danielle

rickholio · 02-06-2004, 03:43 AM

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.

02-06-2004, 02:51 AM	#1
Danielle Confirmed User Join Date: Jun 2002 Location: My Coffin Posts: 1,227	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

02-06-2004, 03:00 AM	#2
swedguy Confirmed User Industry Role: Join Date: Jan 2002 Posts: 7,981	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.

02-06-2004, 03:06 AM	#3
rickholio Confirmed User Industry Role: Join Date: Jan 2004 Location: Nor'easterland Posts: 1,914	select count(distinct surfer_ip), page_visited from clubd_counter_data group by page_visited Should aughta do the trick.

02-06-2004, 03:10 AM	#4
rickholio Confirmed User Industry Role: Join Date: Jan 2004 Location: Nor'easterland Posts: 1,914	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.

02-06-2004, 03:43 AM	#7
rickholio Confirmed User Industry Role: Join Date: Jan 2004 Location: Nor'easterland Posts: 1,914	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.