CODEWITHSUNDEEP
postgresql
sergeda
sergeda

Reputation: 2201

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 I want to count how many urls for each domain. I try to do this like this:

SELECT
    domain.name AS dn,
    SELECT COUNT(*) FROM url WHERE url.url ILIKE '%' || dn || '%'

but without luck. Got Syntax error. How to make it right?

Upvotes: 1

Views: 1500

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

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

Extracting the domain from the url will avoid matches where the domain appears in the path or query string parts.

Upvotes: 1

Andy Nichols
Andy Nichols

Reputation: 3002

You'll need to do a join between the tables and then group by the domain name. This leads to

SELECT d.name as dn, COUNT(*) as number_of_urls
FROM domain d
INNER JOIN url u ON u.url ILIKE '%'||d.name||'%'
GROUP BY d.name

This will NOT give a row for any domains with zero entries in the url table. If you need these rows change the INNER JOIN to a LEFT OUTER JOIN

Upvotes: 2

Related Questions