Need help constructing query ...
I have a table of full URLs and IPs and am using the following query to return distinct web requests by domain. Using SUBSTRING_INDEX it only returns the domain part of the URL: SELECT SUBSTRING_INDEX(url, '/', 3) as topsites, count(distinct ip) as count from tablename WHERE SUBSTRING_INDEX(url, '/', 3) LIKE '%mydomain%' group by topsites order by count Example output: topsitescount http://www.mydomain.com5 http://mydomain.com 3 My question is how do I modify the query to get it to merge requests for the same domain by ignoring the www. so that the above would return: http://mydomain.com 8 I think it has something to do with adding REPLACE('url', 'www.', '') but I can't figure out where to put it to make it work. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
* John Kelly I have a table of full URLs and IPs and am using the following query to return distinct web requests by domain. Using SUBSTRING_INDEX it only returns the domain part of the URL: SELECT SUBSTRING_INDEX(url, '/', 3) as topsites, count(distinct ip) as count from tablename WHERE SUBSTRING_INDEX(url, '/', 3) LIKE '%mydomain%' group by topsites order by count Example output: topsitescount http://www.mydomain.com5 http://mydomain.com 3 My question is how do I modify the query to get it to merge requests for the same domain by ignoring the www. so that the above would return: http://mydomain.com 8 I think it has something to do with adding REPLACE('url', 'www.', '') but I can't figure out where to put it to make it work. Try either SUBSTRING_INDEX(REPLACE(url, 'www.', ''), '/', 3) or REPLACE(SUBSTRING_INDEX(url, '/', 3),'www.', '') You don't need it in the WHERE clause, only in the field list and GROUP BY: SELECT REPLACE(SUBSTRING_INDEX(url, '/', 3),'www.', '') as topsites, count(distinct ip) as count from tablename WHERE SUBSTRING_INDEX(url, '/', 3) LIKE '%mydomain%' group by topsites order by count -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
Try the following: SELECT ip_address, url, count(distinct ip_address) FROM tablename GROUP BY ip_address, url At 09:48 PM 10/21/2003, John Kelly wrote: : -Original Message- : From: John Kelly [mailto:[EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 3:45 PM : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Subject: Re: Need help constructing query ... : : : - Original Message - : From: Daniel Clark [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 2:33 PM : Subject: Re: Need help constructing query ... : : : : Hi, I have a table full of logged urls and ip addresses. : The following : : query returns all the urls and the number of requests. How would I : : modify it to return unique requests based on distinct ip : addresses? : : : : select url, count(*) as pageviews from table group by url order by : : pageviews desc : : : : How about: : : : : SELECT ip_address, url, count(*) : : FROM tablename : : GROUP BY ip_adress, url : : : Thanks but I could not get that to work. It does not appear : to count the number of page requests by distinct IPs anyway : does it? Don't you need something like a : count(distinct(ip_address)) somewhere in there? : : -- - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'John Kelly' [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:57 PM Subject: RE: Need help constructing query ... : Then I think you want : SELECT url, COUNT(DISTINCT ip_address) : FROM tablename : GROUP BY url; Thanks, this must be a resource intensive query as it works in a few seconds on a small table but takes 6+ minutes when done on a table with just 100,000 records. Anyway, thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help constructing query ...
Hi, I have a table full of logged urls and ip addresses. The following query returns all the urls and the number of requests. How would I modify it to return unique requests based on distinct ip addresses? select url, count(*) as pageviews from table group by url order by pageviews desc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
Hi, I have a table full of logged urls and ip addresses. The following query returns all the urls and the number of requests. How would I modify it to return unique requests based on distinct ip addresses? select url, count(*) as pageviews from table group by url order by pageviews desc How about: SELECT ip_address, url, count(*) FROM tablename GROUP BY ip_adress, url -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
- Original Message - From: Daniel Clark [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 2:33 PM Subject: Re: Need help constructing query ... : Hi, I have a table full of logged urls and ip addresses. The following : query returns all the urls and the number of requests. How would I : modify it to return unique requests based on distinct ip addresses? : : select url, count(*) as pageviews from table group by url order by : pageviews desc : : How about: : : SELECT ip_address, url, count(*) : FROM tablename : GROUP BY ip_adress, url : Thanks but I could not get that to work. It does not appear to count the number of page requests by distinct IPs anyway does it? Don't you need something like a count(distinct(ip_address)) somewhere in there? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help constructing query ...
Then I think you want SELECT url, COUNT(DISTINCT ip_address) FROM tablename GROUP BY url; -Original Message- From: John Kelly [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:45 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Need help constructing query ... - Original Message - From: Daniel Clark [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 2:33 PM Subject: Re: Need help constructing query ... : Hi, I have a table full of logged urls and ip addresses. The following : query returns all the urls and the number of requests. How would I : modify it to return unique requests based on distinct ip addresses? : : select url, count(*) as pageviews from table group by url order by : pageviews desc : : How about: : : SELECT ip_address, url, count(*) : FROM tablename : GROUP BY ip_adress, url : Thanks but I could not get that to work. It does not appear to count the number of page requests by distinct IPs anyway does it? Don't you need something like a count(distinct(ip_address)) somewhere in there? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
: -Original Message- : From: John Kelly [mailto:[EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 3:45 PM : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Subject: Re: Need help constructing query ... : : : - Original Message - : From: Daniel Clark [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 2:33 PM : Subject: Re: Need help constructing query ... : : : : Hi, I have a table full of logged urls and ip addresses. : The following : : query returns all the urls and the number of requests. How would I : : modify it to return unique requests based on distinct ip : addresses? : : : : select url, count(*) as pageviews from table group by url order by : : pageviews desc : : : : How about: : : : : SELECT ip_address, url, count(*) : : FROM tablename : : GROUP BY ip_adress, url : : : Thanks but I could not get that to work. It does not appear : to count the number of page requests by distinct IPs anyway : does it? Don't you need something like a : count(distinct(ip_address)) somewhere in there? : : -- - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'John Kelly' [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:57 PM Subject: RE: Need help constructing query ... : Then I think you want : SELECT url, COUNT(DISTINCT ip_address) : FROM tablename : GROUP BY url; Thanks, this must be a resource intensive query as it works in a few seconds on a small table but takes 6+ minutes when done on a table with just 100,000 records. Anyway, thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]