CODEWITHSUNDEEP
sqlgroup-by
El Flaco
El Flaco

Reputation: 13

Select groups given a condition in a variable sql

I must do a query where I select those groups, given by de concatenation between sample and serial that could be defined as household, where at least one in the variable bplcountry = 1

sample serial bplcountry
1      1      2
1      1      1
1      3      2
2      1      2
2      2      2
2      3      2
3      1      2
3      3      2
3      3      1

I have made some research but I'm very amateur on SQL. I get some hint like this:

SELECT *
FROM latinCensus
GROUP BY sample AND serial
HAVING COUNT(bplcountry NOT IN ('1') OR NULL) = 0

Also I got some idea in this way

SELECT *
FROM latinCensus
GROUP BY CONCAT(sample,serial) 
HAVING COUNT(bplcountry NOT IN ('1') OR NULL) = 0

I would expect something like this:

sample serial bplcountry
1      1      2
1      1      1
3      3      2
3      3      1

I will appreciate your help!

Upvotes: 0

Views: 90

Answers (2)

Rajat
Rajat

Reputation: 5803

You haven't tagged your db, but something along these lines should work (can also be expressed using joins)

select sample, serial, bplcountry
from t
where (sample,serial) in (select sample,serial 
                          from t 
                          where bplcountry=1);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want the pairs where bplcountry is 1. You can use window functions:

select lc.*
from (select lc.*,
             sum(case when bplcountry = 1 then 1 else 0 end) over (partition by sample, serial) as cnt_1
      from latincensus lc
     ) lc
where cnt_1 > 0;

Or use exists:

select lc.*
from latincensus lc
where exists (select 1
              from latincensus lc2
              where lc2.sample = lc.sample and lc2.serial = lc.serial and
                    lc2.bplcountry = 1
             );

Upvotes: 1

Related Questions