CODEWITHSUNDEEP
sqlpostgresql
Radityo Tody
Radityo Tody

Reputation: 141

PostgreSQL - Count after filtering conditions

I have these columns on my table:

id       country     
A        Italy
A        France
A        Germany
B        Italy
B        France
C        Italy
C        France

I want to get id with contains ONLY Italy and France, so the result will becoming like this:

id  
B
C

Thanks for helping me

Upvotes: 0

Views: 64

Answers (4)

Evan Allen
Evan Allen

Reputation: 122

Why don't you use this

select distinct(id) 
        from countries
        where country in ('Italy','France');

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

A more general approach in Postgres is to use array for the comparison:

select id
from t
group by id
having array_agg(country order by country) = array['France', 'Italy'];

Here is a db<>fiddle.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can try like below by using exists

     with cte as
(
select 'A' as id , 'Italy' as country union all
select 'A','France' union all
select 'A','Germany' union all
select 'B','Italy' union all
select 'B','France' union all
select 'C' ,'Italy' union all
select 'C','France' 

) select distinct id from cte t1
 where  exists ( select 1 from cte t2
  where t1.id=t2.id  
  having count(distinct country)=2
 )
 and country in ('France','Italy')

output

id
B
C

demo fiddle

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

The easiest way to do this for me is to just aggregate by the id and then assert that the minimum country is France, and the maximum Italy:

SELECT id
FROM yourTable
GROUP BY id
HAVING MIN(country) = 'France' AND MAX(country) = 'Italy' AND
       COUNT(DISTINCT country) = 2;

Upvotes: 1

Related Questions