CODEWITHSUNDEEP
sqlpostgresql
Italo Rodrigo
Italo Rodrigo

Reputation: 1785

Get min and count rows group by id

EDIT I forget an important detail

I have a postgresql table like this:

| id | n_1 | n_2 |
|  1 |   3 |   5 |
|  1 |   2 |   6 |
|  1 |   8 |   4 |
|  1 |   1 |   5 |
|  2 |   4 |   3 |
|  2 |   5 |   1 |

I want to get the min values and the count count only if n_2 >= min(n_1):

| id | n_1 | n_2 | count |
|  1 |   1 |   4 |     4 |
|  2 |   4 |   1 |     0 |

The min number from n_1, min number from n_2 and count total when n_2 >= min(n_1) records from each id.

Any help?

Upvotes: 0

Views: 246

Answers (2)

eshirvana
eshirvana

Reputation: 24568

here how you can do it by grouping them by id :

select  id , min(n_1) ,min(n_2), count(case when n_2 >= min_n_1 then 1 end)
from ( select *, min(n_1) over (partition by id) as min_n_1 from table) t
group by id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think this is just aggregation:

select id, min(n_1), min(n_2), count(*)
from t
group by id;

Upvotes: 1

Related Questions