Return count id's value from multiple rows in one column Postgres-CodePudding

I'm having two tables (relation between themTest_case.id = Test_tag.test_id) like this:

Test_case table

id	name
1	Test name 1
2	Test name 2
3	Test name 3
4	Test name 4

Test_tag table

test_id	tag
1	feature:example1
1	package:Reports
1	QA
2	feature:example1
2	package:Reports
2	QA
3	feature:example1
3	package:Reports
3	QA
4	feature:newexample1
4	package:Charts
4	QA

The database tables and structure were already defined as I'm using a oublic library to push the results.

So, I need to return in the result the count of the id's and the value feature:example1 knowing that is a test that contains the tag package:Reports

So, it should return something like Results

count(id)	tag
3	feature:example1

I already tried some different approaches without success.

How can I do that?

CodePudding user response：

select   count(cnt2)
        ,max(tag)
from               (
                   select   *
                           ,count(case when tag = 'package:Reports'  then 1 end) over (partition by id) as cnt
                           ,case when tag = 'feature:example1' then 1 end                        as cnt2                               
                   from     Test_case join test_tag on Test_case.id = Test_tag.test_id
                   ) t
group by cnt2, cnt
having   cnt2 = 1 and cnt > 0

Fiddle

CodePudding user response：

I think I'm as confused as everyone else, but this is a shot in the dark based on the various comments. There are much easier ways to arrive at this dataset, but I'm trying to read between the lines on your comments:

select
  count (t.test_id), t.tag 
from
  test_case c
  join test_tag t on c.id = t.test_id
where
  t.tag like 'feature%' and
  exists (
    select null
    from test_tag t2
    where t2.test_id = t.test_id and t2.tag = 'package:Reports'
  )
group by
  t.tag