sql server 2008 - SQL Get count with most common part of string -

sql server 2008 - SQL Get count with most common part of string -

i able count of column same values, e.g.

select count(*) count, projectid projects grouping projectid order count desc

so have table this,

projectid projecturl 1 http://www.companya.com/projects/123 2 http://www.companyb.com/projects/124 3 http://www.companya.com/projects/125 4 http://www.companyb.com/projects/126 5 http://www.companya.com/projects/127 now expected result without providing parameter projecturl = http://www.companya.com count = 3 projecturl = http://www.companyb.com count = 2

edit

sorry forgot mention types of urls have in table, urls quiet random though, there urls common. creating project categories, project category url can be,

https://spanish.companyaa2342.com/portal/projecta/projectteama/projectpersona/task/124

but projects there no project team or on, it's bit random :?

i need query more generic.

what url have in common

http://ramdomlanguage.companyname.com/portal/randomname.....

please try:

select col, count(col) cnt from( select substring(projecturl, 0, patindex('%.com/%', projecturl)+4) col tbl )x grouping col

sql fiddle demo

sql sql-server-2008 ssms