CODEWITHSUNDEEP
mysqlsqlgroup-byhavingconditional-aggregation
Irfan
Irfan

Reputation: 61

filter `id` with a specified value in another column and it does not appear more than once on each `id`

I have table user:

user_id  status
1010      1
1010      3
1010      3
1011      5
1011      2
1011      3
1012      3
1012      3

i want to filter user_id with the condition that the number "3" in the status column cannot appear more than once.

Expected output:

user_id
1011

I tried this query:

select * from (
    select user_id, status, 
    count(status) over (partition by status,user_id) as `sc` 
    from df
    )
    where sc<=1
    order by user_id

Upvotes: 1

Views: 41

Answers (2)

nbk
nbk

Reputation: 49373

You can use in MySQL HAVING and a conditional SUM

SELECT `user_id` FROM user GROUP BY user_id HAVING SUM(status=3) <= 1
user_id
1011

fiddle

Upvotes: 1

forpas
forpas

Reputation: 164064

Use aggregation and set the condition in the HAVING clause:

SELECT user_id
FROM df
GROUP BY user_id
HAVING SUM(status = 3) <= 1;

Upvotes: 1

Related Questions