select groups having same value in a column

Question

Group_No	Id	Status
1	101	Active
1	102	Active
2	201	Active
2	202	Approved
2	203	Active
3	301	Inactive
3	302	Active
4	401	Denied
4	402	Denied

I have to select the groups which all Ids have either 'Active' OR 'Approved' value in Status column. I tried below query.

select group_no
from <table>
where status in ('Active','Approved') 
group by group_no 
having(count(distinct status)) = 1;

The desired output should be groups 1 and 2. But I am facing problem since 'Active' and 'Approved' are distinct but they are to be considered as same for my purpose. Please suggest how to achieve desired output.

Please explain more precise your intention. If you want to only accept "Active" and "Approved", why not write something like SELECT ...FROM...WHERE status IN ('Active','Approved') AND group_no NOT IN (SELECT group_no FROM...WHERE status IN ('Inactive','Denied')) ? — Jonas Metzler
– Jonas Metzler, Commented Oct 25, 2022 at 7:45
Remove the WHERE clause, and use HAVING clause to make sure there are no other status values. — jarlh
– jarlh, Commented Oct 25, 2022 at 7:51
@JonasMetzler There are several other statuses apart from 'Inactive' or 'Denied', so its difficult to put then all inside "IN" in the subquery. — user9461026
– user9461026, Commented Oct 25, 2022 at 7:57
Ok, you can of course also write ...NOT IN ('Active','Approved') instead. — Jonas Metzler
– Jonas Metzler, Commented Oct 25, 2022 at 7:58

Mathias Kemeter · Accepted Answer · 2022-10-25 10:39:35Z

3

If you want to avoid sub-selects and want to be flexible in terms of new status to be introduced, you could do something like this:

SELECT group_no
FROM <table>
GROUP BY group_no
HAVING 1 = MIN(CASE WHEN status IN ('Active', 'Approved') THEN 1 ELSE 0 END)

answered Oct 25, 2022 at 10:39

Mathias Kemeter

1,1915 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select groups having same value in a column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related