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 Thanks for your help
(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 | +-----+------+-------+