SQLite Forum

Row count
Login

Row count

(1) By pcurtis on 2020-12-29 13:50:11 [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 [link] [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 |
+-----+------+-------+