* 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: > > topsites count > > http://www.mydomain.com 5 > 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]
