Search code examples
sqlpostgresqlcountsubqueryhaving-clause

Postgresql count query

I have table with this structure

create table job_tags (
    job_id varchar not null,
    tag varchar(50) not null,

    foreign key (job_id) references jobs(id) on delete cascade,
    unique (job_id, tag)
);

I would like to perform a single sql query to get tags related to a specific job and its occurrence among ALL jobs. Right now I have such query which apparently does not work SELECT tag, count(tag) as tagcount FROM job_tags WHERE job_id=$1 GROUP BY tagcount ORDER BY tagcount DESC. It may return only one as tagcount. If anyone knows how to build this query properly I would be very happy! Thank you

Solution

  • I would recommend group by, and filtering with a having clause. Postgres' boolean aggregation functions come handy for this:

    select tag, count(*) as cnt_jobs
from job_tags
group by tag
having bool_or(job_id = $1)
order by cnt_jobs desc