I have a table shown below:

Original data

I wish to subset the data so that I am left with ids with a 0 value in multiple columns including all duplicated rows for the ids with more than one row.

Target data

I have tried

select id, group, year, var1, var2, var3, var4 from tbl where var1 = 0 and var2 = 0  and var3 = 0  and var4 = 0;

This is giving me the first record for id 92403 (in the example above) which I don't want because the other records for this id have non-zero values.

CodePudding user response：

One approach here would be to use an aggregation approach to find all id values whose groups of records all have zero values, for all 4 columns:

WITH cte AS (
    SELECT id, `group`
    FROM tbl
    GROUP BY id, `group`
    HAVING SUM(var1 = 0) = COUNT(*) AND
           SUM(var2 = 0) = COUNT(*) AND
           SUM(var3 = 0) = COUNT(*) AND
           SUM(var4 = 0) = COUNT(*)
)

SELECT *
FROM tbl1
WHERE (id, `group`) IN (SELECT id, `group` FROM cte);

CodePudding user response：

select 
t1.id, t1.group, t1.year, t1.var1, t1.var2, t1.var3, t1.var4
from
table as t1
inner join
(select
id
from
table
group by id having count(1) = 1) as t2 on (t1.id = t2.id)
where
t1.var1 = 0 and t1.var2 = 0  and t1.var3 = 0 and t1.var4 = 0;