CODEWITHSUNDEEP
sql
aviss
aviss

Reputation: 2439

SQL: group and count but only if ALL records are greater than a threshold

I have a mytable in AWS Redshift with user_id and records. I need to count number of records for each user but only if ALL records for this user are greater than a threshold.

mytable:

user_id | records
------------------
0       | 678
0       | 567
1       | 845
1       | 123
1       | 420
2       | 789

Threshold = 400 so the result should be:

user_id | count
------------------
0       | 2
2       | 1

I will appreciate your help!

Upvotes: 0

Views: 481

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You can use group by with having:

select user_id, sum(case when records > 400 then 1 else 0 end) as cnt
from t
group by user_id
having min(records) > 400;

Or using :::

select user_id, sum( (records > 400)::int ) as cnt
from t
group by user_id
having min(records) > 400;

Upvotes: 2

Related Questions