CODEWITHSUNDEEP
sql
Maffa
Maffa

Reputation: 13

Is it possible to group tuples by ID and get only tuples fulfilling a COUNT criteria?

I have a problem where i need to get the number of tuples with a value above a certain threshhold.

My table looks something like this

CREATE TABLE TableName
(
id varchar;
result int;
)

Now I want to group the tuples by id and see the amount of objects where result is above 50, also the amount of objects with any value to result. I know COUNT exists but I can't seem to work out how to use it in this context.

Any help is highly appreciated!

Upvotes: 1

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If I understand correctly, you can use conditional aggregation:

select id, count(*) as num_rows,
       sum(case when value > 50 then 1 else 0 end) as num_rows_value_50
from t
group by id;

Upvotes: 1

Related Questions