Sqlite: How to get the percentage of a total as a column

Question

I have a table with sequences and their total occurrence and I'd like to create a column which shows the percentage of the total of all sequences.

This is the basic table:

sequence

e3a0b000
e92d4030
e59f3038
e1a0c00d
e1a0c00d
e1a0c00d
e1a0c00d
...

SELECT sequence, Count(*) AS total
FROM seq_one
GROUP BY sequence
ORDER BY total DESC

Which then gives me this:

sequence | total

e1a0c00d | 155
e3510000 | 2
e2512001 | 1
e3a0b000 | 1
e59f3038 | 1
e92d4010 | 1
e92d4030 | 1

Then applying this to the temporary table:

SELECT sequence, total, total*1.0/SUM(total) as relative
FROM (SELECT sequence, Count(*) AS total
      FROM seq_one
      GROUP BY sequence
      ORDER BY total DESC)

Gives me this:

sequence | total | relative
e1a0c00d | 155 | 0.95679012345679

I would like to get the whole column instead of only the first row.

[ratio_to_report function](https://stackoverflow.com/a/35976447/5070879) - SQLite supports windowed functions — Lukasz Szozda, Apr 13 '19 at 09:37
@TimBiegeleisen Yes, windowed functions were introduced with version [SQLite Release 3.25.0](https://stackoverflow.com/questions/16847574/how-to-use-row-number-in-sqlite/51863033#51863033) — Lukasz Szozda, Apr 13 '19 at 12:55
Possible duplicate of [Postgres ratio\_to\_report function](https://stackoverflow.com/questions/35976390/postgres-ratio-to-report-function) — Lukasz Szozda, Apr 13 '19 at 12:55

Sqlite: How to get the percentage of a total as a column

0 Answers0