mysql regex counting

Question

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.

mind to fix all the typo first?

ajreal
– ajreal

2011-01-11 09:41:37 +00:00
Commented Jan 11, 2011 at 9:41 — ajreal
– ajreal, Commented Jan 11, 2011 at 9:41

ajreal · Accepted Answer · 2011-01-11 11:23:50Z

1

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

answered Jan 11, 2011 at 11:23

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql regex counting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related