Row count

# Row count

### (1) By pcurtis on 2020-12-29 13:50:11 [link] [source]

```I have the following data

IDX 	DATA
1 	a
2 	a
3 	a
4 	b
5 	b
6 	b
7 	b
8 	b
9 	c
10 	c

How can I get the row count as such

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

```

### (2) By anonymous on 2020-12-29 17:54:39 in reply to 1 [link] [source]

``````/* Create the table ... since you have not scripted it!! */
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp (
idx INT,
data VARCHAR(1)
);
/* Populate the table */
INSERT INTO tmp (
idx,
data
)
VALUES (1,'a'),
(2,'a'),
(3,'a'),
(4,'b'),
(5,'b'),
(6,'b'),
(7,'b'),
(8,'b'),
(9,'c'),
(10,'c');
/* Here's one solution */
SELECT a.idx,
a.data,
(b.id || ' /' || c.cnt) AS cnt /* Count is a reserved word */
FROM (
tmp a INNER JOIN (
SELECT (
row_number() OVER (
PARTITION BY data ORDER BY idx ASC
)
) AS id,
*
FROM tmp
) b ON a.idx = b.idx
)
INNER JOIN (
SELECT data,
count(*) AS cnt
FROM tmp
GROUP BY data
) c ON a.data = c.data;
``````

### (3) By David Raymond (dvdraymond) on 2020-12-29 18:55:43 in reply to 1 [source]

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