Gulafsan Shaheen
4 Mar 2022
General
Give the following table:
employee_id | group | probability |
---|---|---|
123 | group1 | 0.9 |
123 | group2 | 0.6 |
45 | group2 | 0.8 |
567 | group2 | 0.56 |
567 | group3 | 0.78 |
567 | group1 | 0.90 |
I need to extract users who are in group 1 and 2, which means I only need to retrieve user 123. My query goes like the following:
with two_groups as (
select employee_id
from table1
where group in ('group1', 'group2')
group by 1
having max(group) <> min(group) and count(employee_id) = 2
)
select *
from two_groups
join table1 using (employee_id)
The reason that I am joining it back to the table1 is because I could not add the group and probability columns as a field in the "two_groups" subquery because I didn't want to group by them.
What Can I do to just extract the users being in just exactly two groups?
Thank you!
No Comments to show
© 2024 Copyrights reserved for web-brackets.com