Select count with LIKE operator in Postgresql -

i have defined these tables:

create table domain (   id          bigserial not null,   name       varchar(255) not null,   primary key (id));  create table url (   id         bigserial not null,   url       text not null,   primary key (id)); 

and want count how many urls each domain. try this:

select     domain.name dn,     select count(*) url url.url ilike '%' || dn || '%' 

but without luck. got syntax error. how make right?

select d.name, count(*)     domain d     inner join     url u on split_part(u.url, '/', 3) '%' || d.name group 1 

extracting domain url avoid matches domain appears in path or query string parts.