Using window functions this might be something like this select idx, data, row_number() over (partition by data order by idx) || ' / ' || count(*) over (partition by data) as count from tmp order by idx; I'm assuming that if the data bits are non-contiguous that it should be overall ranks and counts, and not just within that streak. That is, I assume you want this: +-----+------+-------+ | idx | data | count | +-----+------+-------+ | 1 | a | 1 / 4 | | 2 | a | 2 / 4 | | 3 | a | 3 / 4 | | 4 | b | 1 / 6 | | 5 | b | 2 / 6 | | 6 | b | 3 / 6 | | 7 | b | 4 / 6 | | 8 | b | 5 / 6 | | 9 | c | 1 / 3 | | 10 | c | 2 / 3 | | 11 | a | 4 / 4 | | 12 | b | 6 / 6 | | 13 | c | 3 / 3 | +-----+------+-------+ ... rather than ... +-----+------+-------+ | idx | data | count | +-----+------+-------+ | 1 | a | 1 / 3 | | 2 | a | 2 / 3 | | 3 | a | 3 / 3 | | 4 | b | 1 / 5 | | 5 | b | 2 / 5 | | 6 | b | 3 / 5 | | 7 | b | 4 / 5 | | 8 | b | 5 / 5 | | 9 | c | 1 / 2 | | 10 | c | 2 / 2 | | 11 | a | 1 / 1 | | 12 | b | 1 / 1 | | 13 | c | 1 / 1 | +-----+------+-------+