EN VI

Sql - Group by where at least one group meets condition?

Sql - Group by where at least one group meets condition

We have a table Data with values:

Data:

id | name    | isrequired | isreleased
1  | test    | 0          | 1
1  | data    | 0          | 0
1  | another | 0          | 0
2  | test    | 0          | 0
2  | nan     | 0          | 0
3  | haha    | 0          | 0
4  | hehe    | 1          | 0
4  | jajaj   | 0          | 0

I am trying to return the group which has at least combination of isrequired and isreleased set to 1 (like at least one of isrequired or isreleased should be 1 in row). In this case, the query should return:

Expected output:

1 | test    | 0 | 1
1 | data    | 0 | 0
1 | another | 0 | 0
4 | hehe    | 1 | 0
4 | jajaj   | 0 | 0

Current query is:

select id, name, isrequired, isreleased
from testtable t
group by id, name, isrequired, isreleased
having count(*) > 0 and 
count(case when IsReleased = 0 and Isrequired = 0 then 0 else 1 end) >= 1)

DDL:

CREATE TABLE testtable (
[Id] [int] NOT NULL,
[Name] [varchar](10) NOT NULL,
[IsReleased] [bit] NOT NULL,
[IsRequired] [bit] NOT NULL)

Insert sql:

insert into testtable values (1, 'test', 0, 1);
insert into testtable values (1, 'data', 0, 0);
insert into testtable values (1, 'another', 0, 0);
insert into testtable values (2, 'test', 0, 0);
insert into testtable values (2, 'nan', 0, 0);
insert into testtable values (3, 'haha', 0, 0);
insert into testtable values (4, 'hehe', 1, 0);
insert into testtable values (4, 'jajaj', 0, 0);

Just a bit lost as to what to add next, Thanks.

Solution:

You can use a windowed sum function for this:

with cte as (
    select *
        , sum(convert(int, IsReleased) + convert(int, IsRequired))
            over (partition by id) IsIncluded
    from testtable
)
select *
from cte
where IsIncluded > 0;