How to filter records based on another grouped by column in a same table-CodePudding

I have a sample table as below:

Target output:

I have tried with below script but only number of count produced as output:

select ID, count(COUNTRY) from test
group by COUNTRY
having count(COUNTRY)=3;

Could anyone here please help how can I get the targeted output? Thanks.

CodePudding user response：

Here's one option - a subquery:

Sample data:

SQL> with test (id, name, country) as
  2    (select 1, 'Mike' , 'Australia' from dual union all
  3     select 2, 'Jason', 'Australia' from dual union all
  4     select 3, 'Lee'  , 'China'     from dual union all
  5     select 4, 'Simon', 'India'     from dual union all
  6     select 5, 'Alex' , 'Malaysia'  from dual union all
  7     select 6, 'John' , 'Australia' from dual
  8    )

Query:

  9  select id, country
 10  from test
 11  where country in (select country
 12                    from test
 13                    group by country
 14                    having count(*) = 3
 15                   )
 16  order by id;

        ID COUNTRY
---------- ---------
         1 Australia
         2 Australia
         6 Australia

SQL>

CodePudding user response：

You can use analytic functions and only scan the table once:

SELECT id, name, country
FROM   (
  SELECT id, name, country,
         COUNT(*) OVER (PARTITION BY country) AS people_per_country
  FROM   table_name
)
WHERE  people_per_country = 3;

Which, for the sample data:

CREATE TABLE table_name (id, name, country) AS
  SELECT 1, 'Mike' , 'Australia' FROM DUAL UNION ALL
  SELECT 2, 'Jason', 'Australia' FROM DUAL UNION ALL 
  SELECT 3, 'Lee'  , 'China'     FROM DUAL UNION ALL
  SELECT 4, 'Simon', 'India'     FROM DUAL UNION ALL
  SELECT 5, 'Alex' , 'Malaysia'  FROM DUAL UNION ALL
  SELECT 6, 'John' , 'Australia' FROM DUAL;

Outputs:

ID NAME COUNTRY

1 Mike Australia

2 Jason Australia

6 John Australia

ID	NAME	COUNTRY
1	Mike	Australia
2	Jason	Australia
6	John	Australia

db<>fiddle here