Row count
(1) By pcurtis on 2020-12-29 13:50:11 [link]
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 Thanks for your help
(2) By anonymous on 2020-12-29 17:54:39 in reply to 1 [link]
``` /* 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
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 | +-----+------+-------+