CodingForums.com - View Single Post - Distinct Count of values, but need a wildcard

Old Pedant · 12-31-2012, 07:27 PM

*IF* all the fields contain *ONLY* URLs of the form

Code:

    http(s)://(www.)xxxxx.yyy/anything

Then I think we can do this. But what about a url such as http://download.microsoft.com? That is, a subdomain?

Are you worried about them? I think it would be tough to create a simple expression to look for those, as well.

Ignoring subdomains, this SEEMS to work:

Code:

SELECT 
   SUBSTR(
       REPLACE(REPLACE(REPLACE(CONCAT(url,'/'),'http://',''),'https://',''),'www.',''),
       1,
       LOCATE('/',REPLACE(REPLACE(REPLACE(CONCAT(url,'/'),'http://',''),'https://',''),'www.',''))-1
   ) AS domain, 
   COUNT(*) AS howmany
FROM urls
GROUP BY domain
ORDER BY howmany DESC

But *ONLY* if your URL field contains urls alone, no other text.

12-31-2012, 07:27 PM	PM User \| #4
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,556 Thanks: 62 Thanked 4,054 Times in 4,023 Posts	IF all the fields contain ONLY URLs of the form Code: http(s)://(www.)xxxxx.yyy/anything Then I think we can do this. But what about a url such as `http://download.microsoft.com`? That is, a subdomain? Are you worried about them? I think it would be tough to create a simple expression to look for those, as well. Ignoring subdomains, this SEEMS to work: Code: SELECT SUBSTR( REPLACE(REPLACE(REPLACE(CONCAT(url,'/'),'http://',''),'https://',''),'www.',''), 1, LOCATE('/',REPLACE(REPLACE(REPLACE(CONCAT(url,'/'),'http://',''),'https://',''),'www.',''))-1 ) AS domain, COUNT() AS howmany FROM urls GROUP BY domain ORDER BY howmany DESC But ONLY* if your URL field contains urls alone, no other text. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.