CODEWITHSUNDEEP
sqlpostgresqlcountsubqueryhaving-clause
bredart
bredart

Reputation: 318

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

Upvotes: 0

Views: 54

Answers (2)

Popeye
Popeye

Reputation: 35910

You can use sub-query as follows:

SELECT t.tag, count(*) as tagcount FROM job_tags t
WHERE t.tag in (select tt.tag from job_tags tt where tt.job_id=$1)
GROUP BY t.tag
ORDER BY tagcount DESC;

Upvotes: 2

GMB
GMB

Reputation: 222492

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

Upvotes: 2

Related Questions