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 |
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 |
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
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