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

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!

CodePudding user response：

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;