CODEWITHSUNDEEP
mysqlcount
love
love

Reputation: 1391

mysql regex counting

table links
id       url
 1       http://www.domain.hk/index.php?main_page=index&cPath=8
 3       http://www.domain.com/index.php?main_page=index&cPath=3
 6       http://www.domain.com/index.php?main_page=index&cPath=8
 8       http://www.domain.hk/index.php?main_page=index&cPath=7&language=tc
 9       http://www.domain.com/index.php?main_page=index&cPath=3

How to count number of same cPath id and number of same cPath id's domain from field url use regex ?

I want the results like this:

cPath : 8 total: 2 hk: 1 com : 1

cPath : 3 total: 2 com : 2

cPath : 7 total: 1 com : 1

Thanks a lot.

Upvotes: 0

Views: 323

Answers (1)

ajreal
ajreal

Reputation: 47321

I don't think I have enough knowledge to generate hk:1, com:1

but this is closer I can generate

select 
  substring_index(substring_index(url, 'cPath=', -1), '&', 1) as cpath, 
  count(*) as total, 
  substring_index(substring_index(url, '/', 3), '.', -1) as tld 
from links 
group by cpath, tld;

| cpath | count(*) | tld  |
+-------+----------+------+
| 3     |        2 | com  |
| 7     |        1 | hk   |
| 8     |        1 | com  |
| 8     |        1 | hk   |
+-------+----------+------+

PS hk,com is not domain name but TLD

Upvotes: 1

Related Questions