CODEWITHSUNDEEP
sqlpostgresql
emteka
emteka

Reputation: 23

SQL count distinct minimums of grouped values

How can I count how many times each minimum exists?

I've included example dataset in following link: https://www.db-fiddle.com/f/nUJdzj3ETXb8knMfeqYtRw/0

for example there are six 1's in the table, however only four of them are minimums for corresponding name

desired output would be

minimum count
0 2
1 4
2 1

Upvotes: 2

Views: 58

Answers (3)

icarus
icarus

Reputation: 105

you can use PARTITION BY clause, as follows:

SELECT distinct MIN(id) as minimum, count(name) over (partition by min(id))
FROM testdb
group by name
order by minimum

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can try using corelated subquery

-- minimum for every person

SELECT  id,count(*)
FROM testdb  t1
where id=(select min(id) from testdb t2 where t1.name=t2.name)
GROUP BY id

DEMO

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

You can use group by minimum to get that

select minimum, count(minimum) from (
SELECT name, MIN(id) minimum FROM testdb  
GROUP BY (name))t
group by minimum
order by minimum

db-fiddle link:

https://www.db-fiddle.com/f/nUJdzj3ETXb8knMfeqYtRw/0#&togetherjs=t2hru6IGtw

Output:

minimum count
0 2
1 4
2 1

Upvotes: 1

Related Questions